<?php
	if(isset($_POST['admin5_save'])||isset($_POST['admin5_edit'])){
		$tip 		= (int)htmlspecialchars(addslashes($_POST['tip']));
		$name 		= htmlspecialchars(addslashes($_POST['name']));
		$descrip 	= htmlspecialchars(addslashes($_POST['descrip']));
		$data_s1		= strtotime(htmlspecialchars(addslashes($_POST['data_s1'])));
		$data_s2		= strtotime(htmlspecialchars(addslashes($_POST['data_s2'])));
		$status = 0; if(isset($_POST['status'])) $status = 1;
	}
	if(isset($_POST['admin5_save'])){
		$sql = 'INSERT INTO `'.$TB['otchgods'].'` VALUES (0,'.time().','.$tip.','.$data_s1.','.$data_s2.',"'.$name.'","'.$descrip.'",'.$status.')';
		$rez = $DB->QUR($sql);
		if(!$rez['err']){
			$msg['msg'][]='Запись создана!';
		}else{
			$msg['err'][]='Запись не создана! Ошибка БД!!!';
		}
		$des = 'show';
	}
	if(isset($_POST['admin5_edit'])){
		$id = (int)$_POST['id'];
		$sql = 'UPDATE `'.$TB['otchgods'].'` SET tip='.$tip.',data_s1='.$data_s1.',data_s2='.$data_s2.',name="'.$name.'",descrip="'.$descrip.'",status='.$status.' WHERE id='.$id.' LIMIT 1';
		$rez = $DB->QUR($sql);	
		if(!$rez['err']){
			$msg['msg'][]='Запись отредактирована!';
		}else{
			$msg['err'][]='Запись не отредактирована! Ошибка БД!!!';
		}
		$des = 'show';
	}
	if($des == 'otchet01'){
		$id = (int)$_GET['id'];
		//$CF['user_statuses'][24] Декан
		//$CF['user_statuses'][23] Кафедра
		$sql = 'SELECT * FROM '.$TB['otchgods'].' WHERE id='.$id.'';
		$rez = $DB->QUR_SEL($sql);
		//echo '<pre>'.print_r($rez,1).'</pre>'; exit();
		if($rez){
			$tip = $rez[1]['tip'];//1-деканы 2-кафедры 3-НПР
			$data_s1 = $rez[1]['data_s1'];//дата начала периода
			$data_s2 = $rez[1]['data_s2'];//дата конца периода
			$otch=array();
			//echo $data_s1.'='.$data_s2.'<pre>'.print_r($rez,1).'</pre>';
			if($tip==1){//-деканы
				$_SESSION['otchgods'][1]['data_s1'] = $data_s1;
				$_SESSION['otchgods'][1]['data_s2'] = $data_s2;
				//1.Надо выбрать всех деканов и потом проходить по всем пунктам, т.к. поля расчетные и не все он заполняет!
				$sql = 'SELECT 
					sb.id_user, us.fio, te.dolgnost,te.stepen,te.fakultet,te.kafedra
				FROM 
					'.$ST['dbpf'].'_rejtdek_saves as sb,
					'.$ST['dbpf'].'_users as us,
					'.$ST['dbpf'].'_teachers as te
				WHERE 
					sb.data_c BETWEEN '.$data_s1.' AND '.$data_s2.' AND
					us.id = sb.id_user AND
					te.id_user = us.id
				GROUP BY sb.id_user
				';
				$rez = $DB->QUR_SEL($sql);
				if($rez&&$rez[0]){
					foreach($rez as $key => $val)if($key){
						//расчитываем анкету для каждого пользователя
						//выбираем анкету, возможно вывести из цикла-она одинакова будет для всех
						$sql1 = 'SELECT rkr.name as rname,rki.name as iname, rki.id as idi FROM '.$ST['dbpf'].'_rejtdek_items as rki,'.$ST['dbpf'].'_rejtdek_razdels as rkr WHERE rki.id_razdel=rkr.id';
						$rez1 = $DB->QUR_SEL($sql1);
						if($rez1&&$rez1[0]){
							foreach($rez1 as $key1 => $val1)if($key1){
								$balls = rejtdek_raschet($val1['idi'],$val['id_user']);//возвращает баллы по полю
								$val['balls']=$balls['itogo'];
								$val['rname']=$val1['rname'];
								$val['iname']=$val1['iname'];
								$otch[$val['fakultet']][$val['kafedra']][$val['fio']][$val1['rname']][] = $val;
							}
						}
					}
					otchet01_get3($otch,$data_s1,$data_s2);//отдадим на сохранение данных
				}
			}
			if($tip==2){//-кафедры
				$_SESSION['otchgods'][2]['data_s1'] = $data_s1;
				$_SESSION['otchgods'][2]['data_s2'] = $data_s2;
				//1.Надо выбрать всех зав.кафедрой и потом проходить по всем пунктам, т.к. поля расчетные и не все он заполняет!
				$sql = 'SELECT 
					sb.id_user, us.fio, te.dolgnost,te.stepen,te.fakultet,te.kafedra
				FROM 
					'.$ST['dbpf'].'_rejtkaf_saves as sb,
					'.$ST['dbpf'].'_users as us,
					'.$ST['dbpf'].'_teachers as te
				WHERE 
					sb.data_c BETWEEN '.$data_s1.' AND '.$data_s2.' AND
					us.id = sb.id_user AND
					te.id_user = us.id
				GROUP BY sb.id_user
				';
				$rez = $DB->QUR_SEL($sql);
				if($rez&&$rez[0]){
					foreach($rez as $key => $val)if($key){
						//расчитываем анкету для каждого пользователя
						//выбираем анкету, возможно вывести из цикла-она одинакова будет для всех
						$sql1 = 'SELECT rkr.name as rname,rki.name as iname, rki.id as idi FROM '.$ST['dbpf'].'_rejtkaf_items as rki,'.$ST['dbpf'].'_rejtkaf_razdels as rkr WHERE rki.id_razdel=rkr.id';
						$rez1 = $DB->QUR_SEL($sql1);
						if($rez1&&$rez1[0]){
							foreach($rez1 as $key1 => $val1)if($key1){
								$balls = rejtkaf_raschet($val1['idi'],$val['id_user']);//возвращает баллы по полю
								$val['balls']=$balls['itogo'];
								$val['rname']=$val1['rname'];
								$val['iname']=$val1['iname'];
								$otch[$val['fakultet']][$val['kafedra']][$val['fio']][$val1['rname']][] = $val;
							}
						}
					}
					//echo '<pre>'.print_r($otch,1).'</pre>';
					otchet01_get2($otch,$data_s1,$data_s2);//отдадим на сохранение данных
				}
			}
			if($tip==3||$tip==4){//-НПР
				//подсчитать кол-во баллов для НПР в указанный период
				$sql = 'SELECT 
					sb.id_user,sb.id_razdel,sb.id_item,sb.balls,
					rd.name as rname,
					it.name as iname,
					us.fio,
					te.dolgnost,te.stepen,te.fakultet,te.kafedra
				FROM 
					'.$ST['dbpf'].'_rejt_savesballs as sb,
					'.$ST['dbpf'].'_rejt_razdels as rd,
					'.$ST['dbpf'].'_rejt_items as it,
					'.$ST['dbpf'].'_users as us,
					'.$ST['dbpf'].'_teachers as te
				WHERE 
					sb.data_c BETWEEN '.$data_s1.' AND '.$data_s2.' AND
					rd.id = sb.id_razdel AND
					it.id = sb.id_item AND
					us.id = sb.id_user AND
					te.id_user = us.id
				';
				$rez = $DB->QUR_SEL($sql);
				if($rez&&$rez[0]){
					foreach($rez as $key => $val)if($key){
						$otch[$val['fakultet']][$val['kafedra']][$val['fio']][$val['rname']][] = $val;
					}
					otchet01_get1($otch,$data_s1,$data_s2);//отдадим на сохранение данных
				}
			}
		}
		//echo '<pre>'.print_r($otch,1).'</pre>';
		exit();
	}
	if($des == 'del'){
		$id = (int)$_GET['id'];
		$rez = $DB->QUR_SEL('DELETE FROM '.$TB['otchgods'].' WHERE id='.$id.' LIMIT 1');
		$des = 'show';
	}
	if($des == 'add'){
		$item=array();
		$smarty->assign('item',$item);
	}
	if($des == 'edit'){
		$id = (int)$_GET['id'];
		$sql = 'SELECT * FROM '.$TB['otchgods'].' WHERE id='.$id.'';
		$rez = $DB->QUR_SEL($sql);
		if($rez){
			$smarty->assign('item',$rez[1]);
			
		}else{
			$des = 'show';
		}
	}
	if($des == 'show'){	
		$items=array();
		$sql = 'SELECT * FROM '.$TB['otchgods'].' ORDER BY status DESC,data_s1,data_s2,tip';
		$rez = $DB->QUR_SEL($sql);
		if($rez){
			foreach($rez as $key => $val)if($key){
				$items[] = $val;
			}
		}
		$smarty->assign('items',$items);
	}
	
	$tips=array(
		1=>array('id'=>1,'name'=>'Деканы факультетов и директоры институтов'),
		2=>array('id'=>2,'name'=>'Заведующие кафедрами'),
		3=>array('id'=>3,'name'=>'НПР'),
		4=>array('id'=>4,'name'=>'НПРбаза'),
	);
	
	$smarty->assign('tips',$tips);
	$smarty->assign('page',$page);
	$smarty->assign('des',$des);
	$maincontent = $smarty->fetch('tpl_rejt_main.html');
	
	//http://swblog.ru/articles/programming/sozdaem-otchety-v-excel-na-php.html
function otchet01_get1($data,$data_s1,$data_s2){
	ini_set('display_errors', TRUE);
	ini_set('display_startup_errors', TRUE);
	date_default_timezone_set('Europe/Moscow');
	require_once 'inc/PHPExcel.php';
	$objPHPExcel = new PHPExcel();
	$objPHPExcel->getProperties()->setCreator("Яковлев Александр Леонидович")
								 ->setLastModifiedBy("Яковлев Александр Леонидович")
								 ->setTitle('Годовой отчет НПР "'.date('d.m.Y',$data_s1).'-'.date('d.m.Y',$data_s2).'"')
								 ->setSubject("выгрузка данных с сайта")
								 ->setDescription("сводные данные по НПР")
								 ->setKeywords("alneo.ru alneo php")
								 ->setCategory("эффективный контракт");
{//ШАПКА 0 листа
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:H1');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Годовой отчет НПР "'.date('d.m.Y',$data_s1).'-'.date('d.m.Y',$data_s2).'" дата формирования '.date('H:i d.m.Y'));
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getFont()->setBold(true);
	$row=2; 
	//пишем ШАПКУ 0 листа
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$row, 'ФИО');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$row, 'Баллы');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$row, 'Пункт');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$row, 'Раздел');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$row, 'Должность');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$row, 'Степень');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.$row, 'Факультет');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.$row, 'Кафедра');
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('A'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('A'.$row)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('B'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('B'.$row)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('C'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('C'.$row)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('D'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('D'.$row)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('E'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('E'.$row)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('F'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('F'.$row)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('G'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('G'.$row)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('H'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('H'.$row)->getFont()->setBold(true);
	$row++;
}
{//ШАПКА 1 листа
	$objPHPExcel->createSheet(1);
	$objPHPExcel->setActiveSheetIndex(1)->mergeCells('A1:F1');
	$objPHPExcel->setActiveSheetIndex(1)->setCellValue('A1', 'Годовой отчет НПР "'.date('d.m.Y',$data_s1).'-'.date('d.m.Y',$data_s2).'" дата формирования '.date('H:i d.m.Y').' краткий');
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('A1')->getFont()->setBold(true);
	$row1=2; 
	//пишем ШАПКУ 1 листа
	$objPHPExcel->setActiveSheetIndex(1)->setCellValue('A'.$row1, 'ФИО');
	$objPHPExcel->setActiveSheetIndex(1)->setCellValue('B'.$row1, 'Образовательная деятельность');
	$objPHPExcel->setActiveSheetIndex(1)->setCellValue('C'.$row1, 'Научная работа за отчетный год');
	$objPHPExcel->setActiveSheetIndex(1)->setCellValue('D'.$row1, 'Руководство научно-исследовательской работой студентов');
	$objPHPExcel->setActiveSheetIndex(1)->setCellValue('E'.$row1, 'Факультет');
	$objPHPExcel->setActiveSheetIndex(1)->setCellValue('F'.$row1, 'Кафедра');
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('A'.$row1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('A'.$row1)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('B'.$row1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('B'.$row1)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('C'.$row1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('C'.$row1)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('D'.$row1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('D'.$row1)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('E'.$row1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('E'.$row1)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('F'.$row1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('F'.$row1)->getFont()->setBold(true);
	$row1++;
}
{//ШАПКА 2 листа
	$objPHPExcel->createSheet(2);
	$objPHPExcel->setActiveSheetIndex(2)->mergeCells('A1:E1');
	$objPHPExcel->setActiveSheetIndex(2)->setCellValue('A1', 'Годовой отчет НПР "'.date('d.m.Y',$data_s1).'-'.date('d.m.Y',$data_s2).'" дата формирования '.date('H:i d.m.Y').' по кафедрам');
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('A1')->getFont()->setBold(true);
	$row2=2;                          
	//пишем ШАПКУ 1 листа             
	$objPHPExcel->setActiveSheetIndex(2)->setCellValue('A'.$row2, 'Факультет');
	$objPHPExcel->setActiveSheetIndex(2)->setCellValue('B'.$row2, 'Кафедра');
	$objPHPExcel->setActiveSheetIndex(2)->setCellValue('C'.$row2, 'Образовательная деятельность');
	$objPHPExcel->setActiveSheetIndex(2)->setCellValue('D'.$row2, 'Научная работа за отчетный год');
	$objPHPExcel->setActiveSheetIndex(2)->setCellValue('E'.$row2, 'Руководство научно-исследовательской работой студентов');
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('A'.$row2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('A'.$row2)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('B'.$row2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('B'.$row2)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('C'.$row2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('C'.$row2)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('D'.$row2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('D'.$row2)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('E'.$row2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('E'.$row2)->getFont()->setBold(true);
	$row2++;
}

	$allfak[1]=0; $allfak[2]=0; $allfak[3]=0;
	foreach($data as $fak => $kafs){
		//пишем факультет
		//$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A'.$row.':F'.$row);
		//$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$row, $fak);
		//$objPHPExcel->setActiveSheetIndex(0)->getStyle('A'.$row)->getFont()->setBold(true);
		//$row++;
		//пишем факультет
		//$objPHPExcel->setActiveSheetIndex(1)->mergeCells('A'.$row1.':F'.$row1);
		//$objPHPExcel->setActiveSheetIndex(1)->setCellValue('A'.$row1, $fak);
		//$objPHPExcel->setActiveSheetIndex(1)->getStyle('A'.$row1)->getFont()->setBold(true);
		//$row1++; 
		$allkaf[1]=0; $allkaf[2]=0; $allkaf[3]=0;
		foreach($kafs as $kaf => $sotruds){
			//пишем кафедру
			//$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A'.$row.':F'.$row);
			//$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$row, $kaf);
			//$objPHPExcel->setActiveSheetIndex(0)->getStyle('A'.$row)->getFont()->setBold(true);
			//$row++;//пишем кафедру
			//$objPHPExcel->setActiveSheetIndex(1)->mergeCells('A'.$row1.':F'.$row1);
			//$objPHPExcel->setActiveSheetIndex(1)->setCellValue('A'.$row1, $kaf);
			//$objPHPExcel->setActiveSheetIndex(1)->getStyle('A'.$row1)->getFont()->setBold(true);
			//$row1++;
			//пишем шапку данных
			//$objPHPExcel->setActiveSheetIndex(1)->setCellValue('A'.$row1, 'ФИО');
			//$objPHPExcel->setActiveSheetIndex(1)->setCellValue('B'.$row1, 'Образовательная деятельность');
			//$objPHPExcel->setActiveSheetIndex(1)->setCellValue('C'.$row1, 'Научная работа за отчетный год');
			//$objPHPExcel->setActiveSheetIndex(1)->setCellValue('D'.$row1, 'Руководство научно-исследовательской работой студентов');
			//$row1++;
			foreach($sotruds as $sotrud => $razdels){
				$arr[1]=0; $arr[2]=0; $arr[3]=0;
				foreach($razdels as $razdel => $values){	
					foreach($values as $key => $val){
						//пишем сотрудника и данные
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$row, $sotrud);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$row, $val['balls']);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$row, $val['iname']);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$row, $razdel);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$row, $val['dolgnost']);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$row, $val['stepen']);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.$row, $fak);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.$row, $kaf);
						$row++;
						if($razdel=='Образовательная деятельность') $arr[1]+=$val['balls'];
						if($razdel=='Научная работа за отчетный год') $arr[2]+=$val['balls'];
						if($razdel=='Руководство научно-исследовательской работой студентов') $arr[3]+=$val['balls'];
					}
				}
				//пишем сотрудника и данные
				$objPHPExcel->setActiveSheetIndex(1)->setCellValue('A'.$row1, $sotrud);
				$objPHPExcel->setActiveSheetIndex(1)->setCellValue('B'.$row1, $arr[1]);
				$objPHPExcel->setActiveSheetIndex(1)->setCellValue('C'.$row1, $arr[2]);
				$objPHPExcel->setActiveSheetIndex(1)->setCellValue('D'.$row1, $arr[3]);
				$objPHPExcel->setActiveSheetIndex(1)->setCellValue('E'.$row1, $fak);
				$objPHPExcel->setActiveSheetIndex(1)->setCellValue('F'.$row1, $kaf);
				$row1++;
				$allkaf[1]+=$arr[1]; $allkaf[2]+=$arr[2]; $allkaf[3]+=$arr[3];
			}
			//пишем итоговую строку по кафедре
			//$objPHPExcel->setActiveSheetIndex(1)->setCellValue('A'.$row1, 'итого по кафедре');
			$objPHPExcel->setActiveSheetIndex(2)->setCellValue('A'.$row2, $fak);
			$objPHPExcel->setActiveSheetIndex(2)->setCellValue('B'.$row2, $kaf);
			$objPHPExcel->setActiveSheetIndex(2)->setCellValue('C'.$row2, $allkaf[1]);
			$objPHPExcel->setActiveSheetIndex(2)->setCellValue('D'.$row2, $allkaf[2]);
			$objPHPExcel->setActiveSheetIndex(2)->setCellValue('E'.$row2, $allkaf[3]);
			$row2++;
			$allfak[1]+=$allkaf[1]; $allfak[2]+=$allkaf[2]; $allfak[3]+=$allkaf[3];
			$allkaf[1]=0; $allkaf[2]=0; $allkaf[3]=0;
		}
		//пишем итоговую строку по факультету
		//$objPHPExcel->setActiveSheetIndex(1)->setCellValue('A'.$row1, 'итого по факультету');
		//$objPHPExcel->setActiveSheetIndex(1)->setCellValue('J'.($row1-1), $allfak[1]);
		//$objPHPExcel->setActiveSheetIndex(1)->setCellValue('K'.($row1-1), $allfak[2]);
		//$objPHPExcel->setActiveSheetIndex(1)->setCellValue('L'.($row1-1), $allfak[3]);
		//$row1++;
		$allfak[1]=0; $allfak[2]=0; $allfak[3]=0;
	}
	
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setAutoSize(true);
	
	$objPHPExcel->setActiveSheetIndex(1)->getColumnDimension('A')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(1)->getColumnDimension('B')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(1)->getColumnDimension('C')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(1)->getColumnDimension('D')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(1)->getColumnDimension('E')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(1)->getColumnDimension('F')->setAutoSize(true);
	
	$objPHPExcel->setActiveSheetIndex(2)->getColumnDimension('A')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(2)->getColumnDimension('B')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(2)->getColumnDimension('C')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(2)->getColumnDimension('D')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(2)->getColumnDimension('E')->setAutoSize(true);
	
	//$objPHPExcel->setActiveSheetIndex(1)->getColumnDimension('B')->setWidth(20);
	
	$head01 = array(
		'font'=>array('bold'=>true,'color'=>array('rgb'=>'000000')),
		'alignment'=>array('horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'wrap'=>true),
	);
	$head02 = array(
		'font'=>array('bold'=>true,'color'=>array('rgb'=>'000000')),
		'alignment'=>array('horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'wrap'=>true),
		'borders'=>array(
			'outline' => array('style'=>PHPExcel_Style_Border::BORDER_THICK,'color' => array('rgb'=>'000000')),
			'allborders' => array('style' => PHPExcel_Style_Border::BORDER_THICK,'color' => array('rgb' => '000000')),
		),
		'fill'=>array('type'=>'solid','color'=>array('rgb'=>'beff97')),
	);
	$body01 = array(
		'font'=>array('bold'=>false,'color'=>array('rgb'=>'000000')),
		'alignment'=>array('horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_LEFT,'wrap'=>true),
		'borders'=>array(
			'outline' => array('style'=>PHPExcel_Style_Border::BORDER_THIN,'color' => array('rgb'=>'000000')),
			'allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN,'color' => array('rgb' => '000000')),
		),
		'fill'=>array('type'=>'solid','color'=>array('rgb'=>'ffffff')),
	);
	
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->applyFromArray($head01);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('A1')->applyFromArray($head01);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('A1')->applyFromArray($head01);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('A2:H2')->applyFromArray($head02);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('A2:F2')->applyFromArray($head02);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('A2:E2')->applyFromArray($head02);
	
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('A3:H'.$row)->applyFromArray($body01);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('A3:F'.$row1)->applyFromArray($body01);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('A3:E'.$row2)->applyFromArray($body01);
	
	
	$objPHPExcel->setActiveSheetIndex(0)->setTitle('Рейтинг НПР');
	$objPHPExcel->setActiveSheetIndex(1)->setTitle('Рейтинг НПР сокращенный');
	$objPHPExcel->setActiveSheetIndex(2)->setTitle('Рейтинг НПР кафедр');
	$objPHPExcel->setActiveSheetIndex(0);

	$file = 'Рейтинг_НПР_'.date('d.m.Y',$data_s1).'-'.date('d.m.Y',$data_s2).'_'.date('H:i d.m.Y');

	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header('Content-Disposition: attachment;filename="'.$file.'.xlsx"');
	header('Cache-Control: max-age=0');
	header('Cache-Control: max-age=1');
	header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
	header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
	header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
	header ('Pragma: public'); // HTTP/1.0
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter->save('php://output');
	exit;
}
function otchet01_get2($data,$data_s1,$data_s2){
	//echo '<pre>'.print_r($data,1).'</pre>';exit();
	ini_set('display_errors', TRUE);
	ini_set('display_startup_errors', TRUE);
	date_default_timezone_set('Europe/Moscow');
	require_once 'inc/PHPExcel.php';
	$objPHPExcel = new PHPExcel();
	$objPHPExcel->getProperties()->setCreator("Яковлев Александр Леонидович")
								 ->setLastModifiedBy("Яковлев Александр Леонидович")
								 ->setTitle('Годовой отчет кафедр "'.date('d.m.Y',$data_s1).'-'.date('d.m.Y',$data_s2).'"')
								 ->setSubject("выгрузка данных с сайта")
								 ->setDescription("сводные данные по НПР")
								 ->setKeywords("alneo.ru alneo php")
								 ->setCategory("эффективный контракт");
{//ШАПКА 0 листа
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:H1');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Годовой отчет кафедр "'.date('d.m.Y',$data_s1).'-'.date('d.m.Y',$data_s2).'" дата формирования '.date('H:i d.m.Y'));
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getFont()->setBold(true);
	$row=2; 
	//пишем ШАПКУ 0 листа
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$row, 'ФИО');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$row, 'Баллы');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$row, 'Пункт');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$row, 'Раздел');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$row, 'Должность');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$row, 'Степень');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.$row, 'Факультет');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.$row, 'Кафедра');
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('A'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('A'.$row)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('B'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('B'.$row)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('C'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('C'.$row)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('D'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('D'.$row)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('E'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('E'.$row)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('F'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('F'.$row)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('G'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('G'.$row)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('H'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('H'.$row)->getFont()->setBold(true);
	$row++;
}
{//ШАПКА 1 листа
	$objPHPExcel->createSheet(1);
	$objPHPExcel->setActiveSheetIndex(1)->mergeCells('A1:F1');
	$objPHPExcel->setActiveSheetIndex(1)->setCellValue('A1', 'Годовой отчет кафедр "'.date('d.m.Y',$data_s1).'-'.date('d.m.Y',$data_s2).'" дата формирования '.date('H:i d.m.Y').' краткий');
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('A1')->getFont()->setBold(true);
	$row1=2; 
	//пишем ШАПКУ 1 листа
	$objPHPExcel->setActiveSheetIndex(1)->setCellValue('A'.$row1, 'ФИО');
	$objPHPExcel->setActiveSheetIndex(1)->setCellValue('B'.$row1, 'Образовательный блок');
	$objPHPExcel->setActiveSheetIndex(1)->setCellValue('C'.$row1, 'Научно-исследовательский блок');
	$objPHPExcel->setActiveSheetIndex(1)->setCellValue('D'.$row1, 'Организационно-воспитательный блок');
	$objPHPExcel->setActiveSheetIndex(1)->setCellValue('E'.$row1, 'Факультет');
	$objPHPExcel->setActiveSheetIndex(1)->setCellValue('F'.$row1, 'Кафедра');
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('A'.$row1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('A'.$row1)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('B'.$row1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('B'.$row1)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('C'.$row1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('C'.$row1)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('D'.$row1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('D'.$row1)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('E'.$row1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('E'.$row1)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('F'.$row1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('F'.$row1)->getFont()->setBold(true);
	$row1++;
}
{//ШАПКА 2 листа
	$objPHPExcel->createSheet(2);
	$objPHPExcel->setActiveSheetIndex(2)->mergeCells('A1:E1');
	$objPHPExcel->setActiveSheetIndex(2)->setCellValue('A1', 'Годовой отчет кафедр "'.date('d.m.Y',$data_s1).'-'.date('d.m.Y',$data_s2).'" дата формирования '.date('H:i d.m.Y').' по кафедрам');
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('A1')->getFont()->setBold(true);
	$row2=2;                          
	//пишем ШАПКУ 1 листа             
	$objPHPExcel->setActiveSheetIndex(2)->setCellValue('A'.$row2, 'Факультет');
	$objPHPExcel->setActiveSheetIndex(2)->setCellValue('B'.$row2, 'Кафедра');
	$objPHPExcel->setActiveSheetIndex(2)->setCellValue('C'.$row2, 'Образовательный блок');
	$objPHPExcel->setActiveSheetIndex(2)->setCellValue('D'.$row2, 'Научно-исследовательский блок');
	$objPHPExcel->setActiveSheetIndex(2)->setCellValue('E'.$row2, 'Организационно-воспитательный блок');
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('A'.$row2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('A'.$row2)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('B'.$row2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('B'.$row2)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('C'.$row2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('C'.$row2)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('D'.$row2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('D'.$row2)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('E'.$row2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('E'.$row2)->getFont()->setBold(true);
	$row2++;
}

	$allfak[1]=0; $allfak[2]=0; $allfak[3]=0;
	foreach($data as $fak => $kafs){
		$allkaf[1]=0; $allkaf[2]=0; $allkaf[3]=0;
		foreach($kafs as $kaf => $sotruds){
			foreach($sotruds as $sotrud => $razdels){
				$arr[1]=0; $arr[2]=0; $arr[3]=0;
				foreach($razdels as $razdel => $values){	
					foreach($values as $key => $val){
						//пишем сотрудника и данные
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$row, $sotrud);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$row, $val['balls']);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$row, $val['iname']);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$row, $razdel);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$row, $val['dolgnost']);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$row, $val['stepen']);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.$row, $fak);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.$row, $kaf);
						$row++;
						if($razdel=='Образовательный блок') $arr[1]+=$val['balls'];
						if($razdel=='Научно-исследовательский блок') $arr[2]+=$val['balls'];
						if($razdel=='Организационно-воспитательный блок') $arr[3]+=$val['balls'];
					}
				}
				//пишем сотрудника и данные
				$objPHPExcel->setActiveSheetIndex(1)->setCellValue('A'.$row1, $sotrud);
				$objPHPExcel->setActiveSheetIndex(1)->setCellValue('B'.$row1, $arr[1]);
				$objPHPExcel->setActiveSheetIndex(1)->setCellValue('C'.$row1, $arr[2]);
				$objPHPExcel->setActiveSheetIndex(1)->setCellValue('D'.$row1, $arr[3]);
				$objPHPExcel->setActiveSheetIndex(1)->setCellValue('E'.$row1, $fak);
				$objPHPExcel->setActiveSheetIndex(1)->setCellValue('F'.$row1, $kaf);
				$row1++;
				$allkaf[1]+=$arr[1]; $allkaf[2]+=$arr[2]; $allkaf[3]+=$arr[3];
			}
			//пишем итоговую строку по кафедре
			$objPHPExcel->setActiveSheetIndex(2)->setCellValue('A'.$row2, $fak);
			$objPHPExcel->setActiveSheetIndex(2)->setCellValue('B'.$row2, $kaf);
			$objPHPExcel->setActiveSheetIndex(2)->setCellValue('C'.$row2, $allkaf[1]);
			$objPHPExcel->setActiveSheetIndex(2)->setCellValue('D'.$row2, $allkaf[2]);
			$objPHPExcel->setActiveSheetIndex(2)->setCellValue('E'.$row2, $allkaf[3]);
			$row2++;
			$allfak[1]+=$allkaf[1]; $allfak[2]+=$allkaf[2]; $allfak[3]+=$allkaf[3];
			$allkaf[1]=0; $allkaf[2]=0; $allkaf[3]=0;
		}
		$allfak[1]=0; $allfak[2]=0; $allfak[3]=0;
	}
	
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setAutoSize(true);
	
	$objPHPExcel->setActiveSheetIndex(1)->getColumnDimension('A')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(1)->getColumnDimension('B')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(1)->getColumnDimension('C')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(1)->getColumnDimension('D')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(1)->getColumnDimension('E')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(1)->getColumnDimension('F')->setAutoSize(true);
	
	$objPHPExcel->setActiveSheetIndex(2)->getColumnDimension('A')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(2)->getColumnDimension('B')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(2)->getColumnDimension('C')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(2)->getColumnDimension('D')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(2)->getColumnDimension('E')->setAutoSize(true);
	
	//$objPHPExcel->setActiveSheetIndex(1)->getColumnDimension('B')->setWidth(20);
	
	$head01 = array(
		'font'=>array('bold'=>true,'color'=>array('rgb'=>'000000')),
		'alignment'=>array('horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'wrap'=>true),
	);
	$head02 = array(
		'font'=>array('bold'=>true,'color'=>array('rgb'=>'000000')),
		'alignment'=>array('horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'wrap'=>true),
		'borders'=>array(
			'outline' => array('style'=>PHPExcel_Style_Border::BORDER_THICK,'color' => array('rgb'=>'000000')),
			'allborders' => array('style' => PHPExcel_Style_Border::BORDER_THICK,'color' => array('rgb' => '000000')),
		),
		'fill'=>array('type'=>'solid','color'=>array('rgb'=>'beff97')),
	);
	$body01 = array(
		'font'=>array('bold'=>false,'color'=>array('rgb'=>'000000')),
		'alignment'=>array('horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_LEFT,'wrap'=>true),
		'borders'=>array(
			'outline' => array('style'=>PHPExcel_Style_Border::BORDER_THIN,'color' => array('rgb'=>'000000')),
			'allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN,'color' => array('rgb' => '000000')),
		),
		'fill'=>array('type'=>'solid','color'=>array('rgb'=>'ffffff')),
	);
	
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->applyFromArray($head01);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('A1')->applyFromArray($head01);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('A1')->applyFromArray($head01);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('A2:H2')->applyFromArray($head02);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('A2:F2')->applyFromArray($head02);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('A2:E2')->applyFromArray($head02);
	
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('A3:H'.$row)->applyFromArray($body01);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('A3:F'.$row1)->applyFromArray($body01);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('A3:E'.$row2)->applyFromArray($body01);
	
	
	$objPHPExcel->setActiveSheetIndex(0)->setTitle('Рейтинг кафедр');
	$objPHPExcel->setActiveSheetIndex(1)->setTitle('Рейтинг кафедр сокращенный');
	$objPHPExcel->setActiveSheetIndex(2)->setTitle('Рейтинг кафедр 1');
	$objPHPExcel->setActiveSheetIndex(0);

	$file = 'Рейтинг_КАФЕДР_'.date('d.m.Y',$data_s1).'-'.date('d.m.Y',$data_s2).'_'.date('H:i d.m.Y');

	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header('Content-Disposition: attachment;filename="'.$file.'.xlsx"');
	header('Cache-Control: max-age=0');
	header('Cache-Control: max-age=1');
	header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
	header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
	header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
	header ('Pragma: public'); // HTTP/1.0
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter->save('php://output');
	exit;
}
function otchet01_get3($data,$data_s1,$data_s2){
	//echo '<pre>'.print_r($data,1).'</pre>';exit();
	ini_set('display_errors', TRUE);
	ini_set('display_startup_errors', TRUE);
	date_default_timezone_set('Europe/Moscow');
	require_once 'inc/PHPExcel.php';
	$objPHPExcel = new PHPExcel();
	$objPHPExcel->getProperties()->setCreator("Яковлев Александр Леонидович")
								 ->setLastModifiedBy("Яковлев Александр Леонидович")
								 ->setTitle('Годовой отчет деканатов "'.date('d.m.Y',$data_s1).'-'.date('d.m.Y',$data_s2).'"')
								 ->setSubject("выгрузка данных с сайта")
								 ->setDescription("сводные данные по НПР")
								 ->setKeywords("alneo.ru alneo php")
								 ->setCategory("эффективный контракт");
{//ШАПКА 0 листа
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:H1');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Годовой отчет деканатов "'.date('d.m.Y',$data_s1).'-'.date('d.m.Y',$data_s2).'" дата формирования '.date('H:i d.m.Y'));
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getFont()->setBold(true);
	$row=2; 
	//пишем ШАПКУ 0 листа
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$row, 'ФИО');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$row, 'Баллы');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$row, 'Пункт');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$row, 'Раздел');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$row, 'Должность');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$row, 'Степень');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.$row, 'Факультет');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.$row, 'Кафедра');
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('A'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('A'.$row)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('B'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('B'.$row)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('C'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('C'.$row)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('D'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('D'.$row)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('E'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('E'.$row)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('F'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('F'.$row)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('G'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('G'.$row)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('H'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('H'.$row)->getFont()->setBold(true);
	$row++;
}
{//ШАПКА 1 листа
	$objPHPExcel->createSheet(1);
	$objPHPExcel->setActiveSheetIndex(1)->mergeCells('A1:F1');
	$objPHPExcel->setActiveSheetIndex(1)->setCellValue('A1', 'Годовой отчет деканатов "'.date('d.m.Y',$data_s1).'-'.date('d.m.Y',$data_s2).'" дата формирования '.date('H:i d.m.Y').' краткий');
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('A1')->getFont()->setBold(true);
	$row1=2; 
	//пишем ШАПКУ 1 листа
	$objPHPExcel->setActiveSheetIndex(1)->setCellValue('A'.$row1, 'ФИО');
	$objPHPExcel->setActiveSheetIndex(1)->setCellValue('B'.$row1, 'Образовательный блок');
	$objPHPExcel->setActiveSheetIndex(1)->setCellValue('C'.$row1, 'Научно-исследовательский блок');
	$objPHPExcel->setActiveSheetIndex(1)->setCellValue('D'.$row1, 'Организационно-воспитательный блок');
	$objPHPExcel->setActiveSheetIndex(1)->setCellValue('E'.$row1, 'Факультет');
	$objPHPExcel->setActiveSheetIndex(1)->setCellValue('F'.$row1, 'Кафедра');
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('A'.$row1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('A'.$row1)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('B'.$row1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('B'.$row1)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('C'.$row1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('C'.$row1)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('D'.$row1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('D'.$row1)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('E'.$row1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('E'.$row1)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('F'.$row1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('F'.$row1)->getFont()->setBold(true);
	$row1++;
}
{//ШАПКА 2 листа
	$objPHPExcel->createSheet(2);
	$objPHPExcel->setActiveSheetIndex(2)->mergeCells('A1:E1');
	$objPHPExcel->setActiveSheetIndex(2)->setCellValue('A1', 'Годовой отчет деканатов "'.date('d.m.Y',$data_s1).'-'.date('d.m.Y',$data_s2).'" дата формирования '.date('H:i d.m.Y').' по кафедрам');
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('A1')->getFont()->setBold(true);
	$row2=2;                          
	//пишем ШАПКУ 1 листа             
	$objPHPExcel->setActiveSheetIndex(2)->setCellValue('A'.$row2, 'Факультет');
	$objPHPExcel->setActiveSheetIndex(2)->setCellValue('B'.$row2, 'Кафедра');
	$objPHPExcel->setActiveSheetIndex(2)->setCellValue('C'.$row2, 'Образовательный блок');
	$objPHPExcel->setActiveSheetIndex(2)->setCellValue('D'.$row2, 'Научно-исследовательский блок');
	$objPHPExcel->setActiveSheetIndex(2)->setCellValue('E'.$row2, 'Организационно-воспитательный блок');
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('A'.$row2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('A'.$row2)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('B'.$row2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('B'.$row2)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('C'.$row2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('C'.$row2)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('D'.$row2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('D'.$row2)->getFont()->setBold(true);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('E'.$row2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('E'.$row2)->getFont()->setBold(true);
	$row2++;
}

	$allfak[1]=0; $allfak[2]=0; $allfak[3]=0;
	foreach($data as $fak => $kafs){
		$allkaf[1]=0; $allkaf[2]=0; $allkaf[3]=0;
		foreach($kafs as $kaf => $sotruds){
			foreach($sotruds as $sotrud => $razdels){
				$arr[1]=0; $arr[2]=0; $arr[3]=0;
				foreach($razdels as $razdel => $values){	
					foreach($values as $key => $val){
						//пишем сотрудника и данные
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$row, $sotrud);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$row, $val['balls']);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$row, $val['iname']);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$row, $razdel);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$row, $val['dolgnost']);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$row, $val['stepen']);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.$row, $fak);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.$row, $kaf);
						$row++;
						if($razdel=='Образовательный блок') $arr[1]+=$val['balls'];
						if($razdel=='Научно-исследовательский блок') $arr[2]+=$val['balls'];
						if($razdel=='Организационно-воспитательный блок') $arr[3]+=$val['balls'];
					}
				}
				//пишем сотрудника и данные
				$objPHPExcel->setActiveSheetIndex(1)->setCellValue('A'.$row1, $sotrud);
				$objPHPExcel->setActiveSheetIndex(1)->setCellValue('B'.$row1, $arr[1]);
				$objPHPExcel->setActiveSheetIndex(1)->setCellValue('C'.$row1, $arr[2]);
				$objPHPExcel->setActiveSheetIndex(1)->setCellValue('D'.$row1, $arr[3]);
				$objPHPExcel->setActiveSheetIndex(1)->setCellValue('E'.$row1, $fak);
				$objPHPExcel->setActiveSheetIndex(1)->setCellValue('F'.$row1, $kaf);
				$row1++;
				$allkaf[1]+=$arr[1]; $allkaf[2]+=$arr[2]; $allkaf[3]+=$arr[3];
			}
			//пишем итоговую строку по кафедре
			$objPHPExcel->setActiveSheetIndex(2)->setCellValue('A'.$row2, $fak);
			$objPHPExcel->setActiveSheetIndex(2)->setCellValue('B'.$row2, $kaf);
			$objPHPExcel->setActiveSheetIndex(2)->setCellValue('C'.$row2, $allkaf[1]);
			$objPHPExcel->setActiveSheetIndex(2)->setCellValue('D'.$row2, $allkaf[2]);
			$objPHPExcel->setActiveSheetIndex(2)->setCellValue('E'.$row2, $allkaf[3]);
			$row2++;
			$allfak[1]+=$allkaf[1]; $allfak[2]+=$allkaf[2]; $allfak[3]+=$allkaf[3];
			$allkaf[1]=0; $allkaf[2]=0; $allkaf[3]=0;
		}
		$allfak[1]=0; $allfak[2]=0; $allfak[3]=0;
	}
	
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setAutoSize(true);
	
	$objPHPExcel->setActiveSheetIndex(1)->getColumnDimension('A')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(1)->getColumnDimension('B')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(1)->getColumnDimension('C')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(1)->getColumnDimension('D')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(1)->getColumnDimension('E')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(1)->getColumnDimension('F')->setAutoSize(true);
	
	$objPHPExcel->setActiveSheetIndex(2)->getColumnDimension('A')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(2)->getColumnDimension('B')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(2)->getColumnDimension('C')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(2)->getColumnDimension('D')->setAutoSize(true);
	$objPHPExcel->setActiveSheetIndex(2)->getColumnDimension('E')->setAutoSize(true);
	
	//$objPHPExcel->setActiveSheetIndex(1)->getColumnDimension('B')->setWidth(20);
	
	$head01 = array(
		'font'=>array('bold'=>true,'color'=>array('rgb'=>'000000')),
		'alignment'=>array('horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'wrap'=>true),
	);
	$head02 = array(
		'font'=>array('bold'=>true,'color'=>array('rgb'=>'000000')),
		'alignment'=>array('horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'wrap'=>true),
		'borders'=>array(
			'outline' => array('style'=>PHPExcel_Style_Border::BORDER_THICK,'color' => array('rgb'=>'000000')),
			'allborders' => array('style' => PHPExcel_Style_Border::BORDER_THICK,'color' => array('rgb' => '000000')),
		),
		'fill'=>array('type'=>'solid','color'=>array('rgb'=>'beff97')),
	);
	$body01 = array(
		'font'=>array('bold'=>false,'color'=>array('rgb'=>'000000')),
		'alignment'=>array('horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_LEFT,'wrap'=>true),
		'borders'=>array(
			'outline' => array('style'=>PHPExcel_Style_Border::BORDER_THIN,'color' => array('rgb'=>'000000')),
			'allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN,'color' => array('rgb' => '000000')),
		),
		'fill'=>array('type'=>'solid','color'=>array('rgb'=>'ffffff')),
	);
	
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->applyFromArray($head01);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('A1')->applyFromArray($head01);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('A1')->applyFromArray($head01);
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('A2:H2')->applyFromArray($head02);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('A2:F2')->applyFromArray($head02);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('A2:E2')->applyFromArray($head02);
	
	$objPHPExcel->setActiveSheetIndex(0)->getStyle('A3:H'.$row)->applyFromArray($body01);
	$objPHPExcel->setActiveSheetIndex(1)->getStyle('A3:F'.$row1)->applyFromArray($body01);
	$objPHPExcel->setActiveSheetIndex(2)->getStyle('A3:E'.$row2)->applyFromArray($body01);
	
	
	$objPHPExcel->setActiveSheetIndex(0)->setTitle('Рейтинг деканатов');
	$objPHPExcel->setActiveSheetIndex(1)->setTitle('Рейтинг деканатов сокращенный');
	$objPHPExcel->setActiveSheetIndex(2)->setTitle('Рейтинг деканатов 1');
	$objPHPExcel->setActiveSheetIndex(0);

	$file = 'Рейтинг_ДЕКАНАТОВ_'.date('d.m.Y',$data_s1).'-'.date('d.m.Y',$data_s2).'_'.date('H:i d.m.Y');

	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header('Content-Disposition: attachment;filename="'.$file.'.xlsx"');
	header('Cache-Control: max-age=0');
	header('Cache-Control: max-age=1');
	header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
	header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
	header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
	header ('Pragma: public'); // HTTP/1.0
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter->save('php://output');
	exit;
}
function otchet01_get1_old($data,$data_s1,$data_s2){//выводит все вместе не очень удобно
	ini_set('display_errors', TRUE);
	ini_set('display_startup_errors', TRUE);
	date_default_timezone_set('Europe/Moscow');
	require_once 'inc/PHPExcel.php';
	$objPHPExcel = new PHPExcel();
	$objPHPExcel->getProperties()->setCreator("Яковлев Александр Леонидович")
								 ->setLastModifiedBy("Яковлев Александр Леонидович")
								 ->setTitle('Годовой отчет НПР "'.date('d.m.Y',$data_s1).'-'.date('d.m.Y',$data_s2).'"')
								 ->setSubject("выгрузка данных с сайта")
								 ->setDescription("сводные данные по НПР")
								 ->setKeywords("alneo.ru alneo php")
								 ->setCategory("эффективный контракт");

	$objPHPExcel->getActiveSheet()->mergeCells('A1:F1');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Годовой отчет НПР "'.date('d.m.Y',$data_s1).'-'.date('d.m.Y',$data_s2).'" дата формирования '.date('H:i d.m.Y'));
	$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
	$row=2;
	foreach($data as $fak => $kafs){
		//пишем факультет
		$objPHPExcel->getActiveSheet()->mergeCells('A'.$row.':F'.$row);
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$row, $fak);
		$objPHPExcel->getActiveSheet()->getStyle('A'.$row)->getFont()->setBold(true);
		$row++;
		foreach($kafs as $kaf => $sotruds){
			//пишем кафедру
			$objPHPExcel->getActiveSheet()->mergeCells('A'.$row.':F'.$row);
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$row, $kaf);
			$objPHPExcel->getActiveSheet()->getStyle('A'.$row)->getFont()->setBold(true);
			$row++;
			foreach($sotruds as $sotrud => $razdels){
				foreach($razdels as $razdel => $values){
					foreach($values as $key => $val){
						//пишем сотрудника и данные
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$row, $sotrud);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$row, $val['dolgnost']);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$row, $val['stepen']);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$row, $razdel);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$row, $val['iname']);
						$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$row, $val['balls']);
						$row++;
					}
				}
			}
		}
	}
	
	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);


	$objPHPExcel->getActiveSheet()->setTitle('Рейтинг НПР');
	$objPHPExcel->setActiveSheetIndex(0);

	$file = 'Рейтинг_НПР_'.date('d.m.Y',$data_s1).'-'.date('d.m.Y',$data_s2).'_'.date('H:i d.m.Y');

	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header('Content-Disposition: attachment;filename="'.$file.'.xlsx"');
	header('Cache-Control: max-age=0');
	header('Cache-Control: max-age=1');
	header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
	header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
	header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
	header ('Pragma: public'); // HTTP/1.0
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter->save('php://output');
	exit;
}