<?php
require("lib/config.inc.php3");
include 'admin/lib/PHPExcel.php';
include 'admin/lib/PHPExcel/Writer/Excel2007.php';

mysql_connect($G_db_host, $G_db_user, $G_db_password);
mysql_select_db($G_db_name);
mysql_query("set names 'utf8'");

function br2nl($string){
    return preg_replace('/\<br(\s*)?\/?\>/i', "\n", $string);
}

$curr = array();
$sql_cur = "SELECT `id`, `value` FROM currency";
$res_cur = mysql_query($sql_cur);
while ($row_cur = mysql_fetch_assoc($res_cur)){
	$curr[$row_cur['id']] = $row_cur['value'];
}

function get_price($price, $cur){
	global $curr;

	if ($price =='999999')
		return '999999';
	elseif (!empty($curr[$cur]))
		return round(($price * $curr[$cur]), 2);
	else
		return $price;
}

function get_tree($id = 0){
	$data = false;
	$sql = "SELECT `id`, `name` FROM groups WHERE parent_id=".$id." AND archive!=1 ORDER BY priority";
	$res = mysql_query($sql);
	if (mysql_num_rows($res)>0)
		$data = array();

	while ($row = mysql_fetch_assoc($res)){
		$data[] = $row;

		//РџРѕР»СѓС‡Р°РµРј С‚РѕРІР°СЂС‹
		$sql_goods = "SELECT i.id, i.name, price, text_small, IF(price='999999', 0, price*c.value) AS orderby, currency FROM item i JOIN currency c ON i.currency = c.id WHERE groups_id = ".$row['id'] . "  ORDER BY orderby DESC";
		$res_goods = mysql_query($sql_goods);
		if (mysql_num_rows($res_goods)!==0){
			while ($row_goods = mysql_fetch_assoc($res_goods)){
				$data[sizeof($data)-1]['goods'][] = $row_goods;
			}
		}

		$data[sizeof($data)-1]['subcats'] = get_tree($row['id']);
	}
	return $data;
}

function print_tree_in_array($data, &$array, &$i = 0, $level = 0){

	foreach ($data as $key => $value) {
		$array[$i] = array('type' => 'group', 'name' => htmlspecialchars_decode($value['name']), 'level' => $level);
		$i++;
		if (isset($value['goods']) && is_array($value['goods'])){
			foreach ($value['goods'] as $key => $value) {
				$array[$i] =array('type' => 'good', 'name' => htmlspecialchars_decode($value['name']), 'text_small' => htmlspecialchars_decode(strip_tags(br2nl($value['text_small']))), 'price' => get_price($value['price'], $value['currency']));
				$i++;
			}
		}
		if (isset($value['subcats']) && is_array($value['subcats'])){
			// $out_level = $level++;
			print_tree_in_array($value['subcats'], $array, $i, $level+1);
		}
	}

}

$data = get_tree();
$array = array();
$i = 0;
print_tree_in_array($data, $array, $i); //output - $array
$date = date("d.m.Y");


function get_merge_coordinates($n){
	return "A{$n}:C{$n}";
}


/* Р’Р«Р’РћР” */
	$objPHPExcel = new PHPExcel();


	$objPHPExcel->setActiveSheetIndex(0);
	$objPHPExcel->getActiveSheet()->setTitle('РџСЂР°Р№СЃ-Р»РёСЃС‚');
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,1, '"РљРѕРјРїР°РЅРёСЏ РђР Р“Рћ"' );
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,2, '"РўРµР».: +7 (495) 786-97-99"' );
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,3, 'market@all-projectors.ru' );
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,4, 'www.all-projectors.ru' );
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,5, "РџСЂР°Р№СЃ-Р»РёСЃС‚ РѕС‚ $date" );

$styleBoldArial12     = array('font' => array('bold' => true, 'name' => 'Arial', 'size' => '12'));
$styleBoldArialBlue12 = array('font' => array('bold' => true, 'name' => 'Arial', 'color' => array('rgb' => '1d1dff'), 'size' => '12'));
$styleGroup 		  = array('font' => array('bold' => true, 'name' => 'Arial', 'color' => array('rgb' => 'ffffff'), 'size' => '12'),
							  'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '538dd5')));
$styleGroupSecond     = array('font' => array('bold' => true, 'name' => 'Arial', 'color' => array('rgb' => '294669'), 'size' => '11'));
$styleType			  = array('font' => array('bold' => true, 'name' => 'Arial', 'color' => array('rgb' => '6096d8'), 'size' => '11'));
$styleGood			  = array('font' => array('name' => 'Arial', 'color' => array('rgb' => '000000'), 'size' => '10'));

$objPHPExcel->getActiveSheet()->mergeCells(get_merge_coordinates(1));
$objPHPExcel->getActiveSheet()->mergeCells(get_merge_coordinates(2));
$objPHPExcel->getActiveSheet()->mergeCells(get_merge_coordinates(3));
$objPHPExcel->getActiveSheet()->mergeCells(get_merge_coordinates(4));
$objPHPExcel->getActiveSheet()->mergeCells(get_merge_coordinates(5));

	$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, 1)->applyFromArray($styleBoldArial12);
	$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, 2)->applyFromArray($styleBoldArial12);
	$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, 3)->applyFromArray($styleBoldArialBlue12);
	$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, 4)->applyFromArray($styleBoldArialBlue12);
	$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, 5)->applyFromArray($styleBoldArial12);

	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(80);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);

	$i= 6;

	$objPHPExcel->getActiveSheet()->getStyle('A'.$i.':A'.sizeof($array))->getAlignment()->setWrapText(true);
	$objPHPExcel->getActiveSheet()->getStyle('A'.$i.':A'.sizeof($array))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
	$objPHPExcel->getActiveSheet()->getStyle('B'.$i.':B'.sizeof($array))->getAlignment()->setWrapText(true);
	$objPHPExcel->getActiveSheet()->getStyle('B'.$i.':B'.sizeof($array))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);


// $objPHPExcel->getActiveSheet()->getRowDimension(1)->setOutlineLevel(0);
// $objPHPExcel->getActiveSheet()->getRowDimension(2)->setOutlineLevel(1);
// $objPHPExcel->getActiveSheet()->getRowDimension(3)->setOutlineLevel(1);
// $objPHPExcel->getActiveSheet()->getRowDimension(4)->setOutlineLevel(2);
// $objPHPExcel->getActiveSheet()->getRowDimension(4)->setOutlineLevel(2);


// $objPHPExcel->getActiveSheet()->getRowDimension(1)->setVisible(false);
// $objPHPExcel->getActiveSheet()->getRowDimension(2)->setVisible(false);
// $objPHPExcel->getActiveSheet()->getRowDimension(3)->setVisible(false);

// // $objPHPExcel->getActiveSheet()->getRowDimension(1)->setCollapsed(true);
// // $objPHPExcel->getActiveSheet()->getRowDimension(2)->setCollapsed(true);
// // $objPHPExcel->getActiveSheet()->getRowDimension(3)->setCollapsed(true);
// // $objPHPExcel->getActiveSheet()->getRowDimension(4)->setCollapsed(true);
// // $objPHPExcel->getActiveSheet()->getRowDimension(5)->setCollapsed(true);

	$objPHPExcel->getActiveSheet()->freezePane('A6');


	foreach ($array as $key => $value) {
		if ($value['type']=='group'){
		$objPHPExcel->getActiveSheet()->mergeCells(get_merge_coordinates($i));
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $i,$value['name']);
			if ($value['level']==2){
				$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, $i)->applyFromArray($styleType);
			}
			elseif ($value['level']==1){
				$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, $i)->applyFromArray($styleGroupSecond);
			}
			else{
				$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, $i)->applyFromArray($styleGroup);
			}
		}
		elseif ($value['type']=='good'){
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $i,$value['name']);
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $i,$value['text_small']);


			if ($value['price']=='999999')
				$value['price'] = 'Р·РІРѕРЅРёС‚Рµ';
			else{
				$value['price'] = round($value['price'],2) . " СЂ.";

			}


			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $i,$value['price']);
			$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, $i)->applyFromArray($styleGood);
			$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1, $i)->applyFromArray($styleGood);
			$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(2, $i)->applyFromArray($styleGood);

			// $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
			// $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1, $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
			// $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(2, $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_BOTTOM);
			// $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(2, $i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		}

		$i++;
	}

	$outfilename = 'РїСЂР°Р№СЃ-Р»РёСЃС‚-Р°СЂРіРѕ-'.date('Ymd');

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save('price/'.$outfilename.'.xlsx');


header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$outfilename.'.xlsx"');
header('Cache-Control: max-age=0');
readfile('price/'.$outfilename.'.xlsx');
exit();