<?php

require_once($_SERVER['DOCUMENT_ROOT']."/config.php");
require_once($_SERVER['DOCUMENT_ROOT'].'/vendor/autoload.php');
$date = date('d.m.Y H-i-s');
//$name = 'clients.xlsx';

$columns = $_POST['columns'];


$cl = new Clients();

/*ini_set('display_errors', 1);
error_reporting(E_ALL & ~E_DEPRECATED & ~E_STRICT & ~E_NOTICE);*/
ini_set('max_execution_time', 300);

$usersArr = json_decode($_POST['responsible'], true);
//file_put_contents(__DIR__.'/log.txt', date('H:i:s'));
$usersId = $cl -> getUsersFilter($usersArr, $_POST['user_id'], $_POST['users_admin']);
//var_dump($usersId);

$itogRow = array();
$rows = array();
//file_put_contents(__DIR__.'/log.txt', var_dump($_POST));
//var_dump($_POST);
//$_SESSION['filter_emp'] = $_POST['responsible'];
    $bd_usl_managers = '';
    $bd_usl = '';
    $arrIdUser = array();
    $arrIdAgent = array();
    foreach($usersId as $userId){
        $arrIdUser[] = "who_work = $userId";
        $arrIdAgent[] = "id_agent = $userId";
    }
		$period = 'all';
		$whereTasks = "";
		$bd_period = '';
		$bd_do_period = '';

		if(isset($_POST['period'])){

		    $period = $_POST['period'];
		}
		$onDate = null;
		$offDate = null;
		if(isset($_POST['onDate'])){
		   $onDate = $_POST['onDate'];
		}
		if(isset($_POST['offDate'])){
		    $offDate = $_POST['offDate'];
		}
		$dates = getPeriod($period, $onDate, $offDate);
		$date_between = $dates['between'];

    $array_menedgers = array();

    $departmentClass = new Department;
    $departments = $departmentClass->getDepartments();
    //var_dump($departments);

    $users = array();
    $sql_users = "SELECT id, agency, manager, agency_name, first_name, last_name, middle_name, id_manager, role_id FROM users WHERE id in (". implode(', ', $usersId).")";

    $q_users = mysql_query($sql_users);
    $res = array();
    $result = array();
    while($r_users = mysql_fetch_assoc($q_users)){
        $users[$r_users['id']] = $r_users;
    }
		$plan = array();
		$year = $dates['years'][0];
		$sql_plan = "SELECT * FROM `sales_plan` where user_id in (". implode(', ', $usersId).") and year = '{$year}'";
		//echo $sql_plan;
		$q_plan = mysql_query($sql_plan);
		while($r_plan = mysql_fetch_assoc($q_plan)){
			$plan[$r_plan['user_id']] = $r_plan;
		}

		$plan_real = array();

    $sql = "SELECT sum(summa) as summa, count(*) as total, who_work, DATE_FORMAT((SELECT date_update FROM `events_clients` WHERE req_id=requisitions.id and event='delete' order by id desc limit 1), '%m') as m, DATE_FORMAT((SELECT date_update FROM `events_clients` WHERE req_id=requisitions.id and event='delete' order by id desc limit 1), '%Y') as y FROM requisitions WHERE cancel=0 and confirm=10 AND  who_work in (".implode(',', $usersId).") and deleted=1 AND ((SELECT date_update FROM `events_clients` WHERE req_id=requisitions.id and event='delete' order by id desc limit 1) $date_between)>0 group by who_work, DATE_FORMAT((SELECT date_update FROM `events_clients` WHERE req_id=requisitions.id and event='delete' order by id desc limit 1), '%m'), DATE_FORMAT((SELECT date_update FROM `events_clients` WHERE req_id=requisitions.id and event='delete' order by id desc limit 1), '%Y')
		UNION
		SELECT sum(summa) as summa, count(*) as total, who_work, DATE_FORMAT((SELECT date_update FROM `events_clients` WHERE client_id=clients.id and event='delete' order by id desc limit 1), '%m') as m, DATE_FORMAT((SELECT date_update FROM `events_clients` WHERE client_id=clients.id and event='delete' order by id desc limit 1), '%Y') as y FROM clients WHERE confirm=10 AND who_work in (".implode(',', $usersId).") and deleted=1 AND ((SELECT date_update FROM `events_clients` WHERE client_id=clients.id and event='delete' order by id desc limit 1) $date_between)>0 group by who_work, DATE_FORMAT((SELECT date_update FROM `events_clients` WHERE client_id=clients.id and event='delete' order by id desc limit 1), '%m'), DATE_FORMAT((SELECT date_update FROM `events_clients` WHERE client_id=clients.id and event='delete' order by id desc limit 1), '%Y')";


		//echo $sql;

    $q = mysql_query($sql);
    while($r = mysql_fetch_assoc($q)){
      $plan_real[] = $r;
    }

		$deposits = array();

		$sql = "SELECT sum(deposit) as deposit, count(*) as total, user_id, DATE_FORMAT(created_at, '%m') as m, DATE_FORMAT(created_at, '%Y') as y FROM deposits_clients_req WHERE user_id in (".implode(',', $usersId).") AND created_at {$date_between} group by user_id, DATE_FORMAT(created_at, '%m'), DATE_FORMAT(created_at, '%Y')";

		$q = mysql_query($sql);
		while($r = mysql_fetch_assoc($q)){
			$deposits[$r['user_id']] = $r;
		}
    $pid=0;
		//var_dump($plan_real);
    foreach($usersId as $key => $id){
        $arrTemp = array();
				//$usersArrM = array();
        $mstr = $users[$id];//mysql_fetch_assoc(mysql_query("SELECT agency, manager, agency_name, first_name, last_name, middle_name, id_manager FROM users WHERE id=".$id));
        if($mstr['agency']){
            $master = "Агентство ". trim($mstr['agency_name']) ." (". trim($mstr['last_name'] . ' ' . $mstr['first_name'] . ' ' . $mstr['middle_name']).")";
            $arrTemp['post'] = 'agency';
            $arrTemp['agency_name'] = $mstr['agency_name'];
						//$arrTemp['usersIdm'] = $usersId;
        }
        else if($mstr['role_id'] > 0 && isset($departments[$mstr['role_id']])){
            $master = trim($mstr['last_name'] . ' ' . $mstr['first_name'] . ' ' . $mstr['middle_name']).' ('.$departments[$mstr['role_id']]['role_name'].' '.$departments[$mstr['role_id']]['dep_name'].')';
            if($mstr['manager']){
                $arrTemp['post'] = 'manager';
							//	$usersArrM[] = 'all_'.$id;
								//$usersIdm = $cl -> getUsersFilter($usersArrM);
								//$arrTemp['usersIdm'] = $usersIdm;

            } else {
                $arrTemp['post'] = 'agent';
								//$arrTemp['usersIdm'][] = $id;

            }

        }
        else if($mstr['manager']){
            $master = "Менеджер " . trim($mstr['last_name'] . ' ' . $mstr['first_name'] . ' ' . $mstr['middle_name']);
            $arrTemp['post'] = 'manager';

						$usersArrM[] = 'all_'.$id;
						//$usersIdm = $cl -> getUsersFilter($usersArrM);
						//$arrTemp['usersIdm'] = $usersIdm;
        }
        else {
            $master = "Агент " . trim($mstr['last_name'] . ' ' . $mstr['first_name'] . ' ' . $mstr['middle_name']);
            $arrTemp['post'] = 'agent';
						//$arrTemp['usersIdm'][] = $id;

        }
        $arrTemp['id_manager'] = $mstr['id_manager'];
        $arrTemp['id'] = $id;
        $arrTemp['fio'] = $master;
        $arrTemp['summa_commission'] = 0;
        $arrTemp['deals_quantity'] = 0;
				$arrTemp['real_summa_commission'] = 0;
				$arrTemp['real_deals_quantity'] = 0;

				$arrTemp['real_pr'] = 0;
				$arrTemp['makings'] = 0;
				$arrTemp['makings_total'] = 0;

        $itogRow['summa_commission'] = 0;
        $itogRow['deals_quantity'] = 0;
				$itogRow['real_summa_commission'] = 0;
				$itogRow['real_deals_quantity'] = 0;

				$itogRow['real_pr'] = 0;
				$itogRow['makings'] = 0;
				$itogRow['makings_total'] = 0;

				foreach($dates['months'] as $month){
					$arrTemp['summa_commission_'.$month] = 0;
					$arrTemp['deals_quantity_'.$month] = 0;
					$arrTemp['real_summa_commission_'.$month] = 0;
					$arrTemp['real_deals_quantity_'.$month] = 0;
					$arrTemp['real_pr_'.$month] = 0;
					$arrTemp['makings_'.$month] = 0;
					$arrTemp['makings_total_'.$month] = 0;
          $itogRow['summa_commission_'.$month] = 0;
          $itogRow['deals_quantity_'.$month] = 0;
          $itogRow['real_summa_commission_'.$month] = 0;
          $itogRow['real_deals_quantity_'.$month] = 0;
          $itogRow['real_pr_'.$month] = 0;
          $itogRow['makings_'.$month] = 0;
          $itogRow['makings_total_'.$month] = 0;
				}

				foreach ($plan as $user_id => $val) {

						//if(in_array($user_id, $arrTemp['usersIdm'])){
						if($user_id == $id){
							$arrTemp['summa_commission'] += (int)$val['summa'];
							$arrTemp['deals_quantity'] += (int)$val['quantity'];


							foreach($dates['months'] as $month){

								if($month == (int)$val['month'] && $dates['years'][0] == $val['year']){
									$arrTemp['summa_commission_'.$month] += (int)$val['summa'];
									$arrTemp['deals_quantity_'.$month] += (int)$val['quantity'];

								}
							}
						}


				}


        foreach ($plan_real as $val) {
					//	if(in_array($user_id, $arrTemp['usersIdm'])){
					if($id == (int)$val['who_work']){
						if($dates['years'][0] == $val['y']){
							$arrTemp['real_summa_commission'] += $val['summa'];
							$arrTemp['real_deals_quantity'] += $val['total'];
						}
							foreach($dates['months'] as $month){
								if($month == (int)$val['m'] && $dates['years'][0] == $val['y']){
									$arrTemp['real_summa_commission_'.$month] += $val['summa'];
									$arrTemp['real_deals_quantity_'.$month] += $val['total'];
								}
							}
						}
				}

				foreach($deposits as $user_id => $val){

					if($user_id == $id){
						if($dates['years'][0] == $val['y']){
							$arrTemp['makings'] += $val['deposit'];
							$arrTemp['makings_total'] += $val['total'];
						}
						foreach($dates['months'] as $month){
							if($month == (int)$val['m'] && $dates['years'][0] == $val['y']){
								$arrTemp['makings_'.$month] += $val['deposit'];
								$arrTemp['makings_total_'.$month] += $val['total'];
							}
						}
					}
				}

				if($arrTemp['real_summa_commission'] > 0 && $arrTemp['summa_commission'] == 0) {
					$arrTemp['real_pr'] = '100%';
				} else if ($arrTemp['real_summa_commission'] > 0 && $arrTemp['summa_commission'] > 0) {
					$arrTemp['real_pr'] = (int)($arrTemp['real_summa_commission']/$arrTemp['summa_commission']*100) .'%';
				} else {
					$arrTemp['real_pr'] = '0%';
				}

				foreach($dates['months'] as $month){
					if($arrTemp['real_summa_commission_'.$month] > 0 && $arrTemp['summa_commission_'.$month] == 0) {
						$arrTemp['real_pr_'.$month] = '100%';
					} else if ($arrTemp['real_summa_commission_'.$month] > 0 && $arrTemp['summa_commission_'.$month] > 0) {
						$arrTemp['real_pr_'.$month] = (int)($arrTemp['real_summa_commission_'.$month]/$arrTemp['summa_commission_'.$month]*100) .'%';
					} else {
						$arrTemp['real_pr_'.$month] = '0%';
					}
				}

				$arrTemp['summa_commission'] = number_format($arrTemp['summa_commission'],0 , '.', ' ');
				//$arrTemp['deals_quantity'] = '<a style="cursor: grab; color: #000; border-bottom: none;">'.$arrTemp['deals_quantity'].'</a>';
				$arrTemp['real_summa_commission'] = number_format($arrTemp['real_summa_commission'],0 , '.', ' ');
				//$arrTemp['real_deals_quantity'] = '<a style="cursor: grab; color: #000; border-bottom: none;">'.'<a style="cursor: grab; color: #000; border-bottom: none;">'.$arrTemp['deals_quantity'].'</a>';


				$arrTemp['makings'] = number_format($arrTemp['makings'],0 , '.', ' ');
			//	$arrTemp['makings_total'] = '<a style="cursor: grab; color: #000; border-bottom: none;">'.$arrTemp['makings_total'].'</a>';
				foreach($dates['months'] as $month){
					$arrTemp['summa_commission_'.$month] = number_format($arrTemp['summa_commission_'.$month],0 , '.', ' ');
					//$arrTemp['deals_quantity_'.$month] = '<a style="cursor: grab; color: #000; border-bottom: none;">'.$arrTemp['deals_quantity_'.$month].'</a>';
					$arrTemp['real_summa_commission_'.$month] = number_format($arrTemp['real_summa_commission_'.$month],0 , '.', ' ');
				//	$arrTemp['real_deals_quantity_'.$month] = '<a style="cursor: grab; color: #000; border-bottom: none;">'.$arrTemp['real_deals_quantity_'.$month].'</a>';
					$arrTemp['makings_'.$month] = number_format($arrTemp['makings_'.$month],0 , '.', ' ');
					//$arrTemp['makings_total_'.$month] = '<a style="cursor: grab; color: #000; border-bottom: none;">'.$arrTemp['makings_total_'.$month].'</a>';
				}

      //  $rows[$id] = $arrTemp;
        $res[] = $arrTemp;
      }

$rows = $res;

//var_dump($columns);

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$sheet->setTitle('План продаж');
$mainArrCol = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
$arrColExccel = array();
$columnStart = 'A';
$rowStart = 3;
$tasksId = false;
$closetId = false;
$tempRow = false;

//echo count($arrColExccel);
//echo count($itogRow)."\n";
$j = 0;
$char1 = -1;
for($e=0; $e<count($itogRow)+1; $e++) {
  $char = '';
  if($char1 > -1){
    $char .= $mainArrCol[$char1];
  }
  $char .= $mainArrCol[$j];
  $arrColExccel[] = $char;
  if($j == count($mainArrCol)-1){
    $char1++;
    $j=-1;
  }

  $j++;
}
//var_dump($arrColExccel);

$arrayHeder = [$columnStart => ['title'=>'№','width'=>5]];
$arrRows = array();
$j = 0;
$arrPodTask = array();

$arrPodCloset = array();

foreach($columns as $col){
    if(isset($col['field'])){

            $tempArr['title'] = $col['title'];
            if($col['field'] == 'fio'){
                $tempArr['width'] = 35;
            } else {
                $tempArr['width'] = 17;
            }
            $arrayHeder[$arrColExccel[$j]] = $tempArr;
            $arrRows[$arrColExccel[$j]] = $col['field'];
            $j++;
          }

}
unset($columns[0]);
unset($columns[1]);
unset($columns[2]);
unset($columns[3]);
unset($columns[4]);
unset($columns[5]);
unset($columns[6]);
unset($columns[7]);
//var_dump($columns);
foreach($dates['months'] as $month){
  if($month != $dates['months'][0]){
    foreach($columns as $col){
        if(isset($col['field'])){
                $tempArr['title'] = $col['title'];
                $tempArr['width'] = 17;
                $arrayHeder[$arrColExccel[$j]] = $tempArr;

                $arrRows[$arrColExccel[$j]] = str_replace($dates['months'][0],$month, $col['field']);
                $j++;
              }

    }
  }
}

$textFilters = '';
if(isset($_POST['period'])){
    $period = $_POST['period'];
    $textFilters .= "Период - ";

    $textFilters .= 'C '.date('d.m.Y', strtotime($dates['dateStart']));
    $textFilters .= ' по '.date('d.m.Y', strtotime($dates['dateEnd']));
}
$mounthArrray = ["", "Январь","Февраль","Март","Апрель","Май","Июнь","Июль","Август","Сентябрь","Октябрь","Ноябрь","Декабрь" ];
//echo 'A'.($rowStart-1).':A'.(($rowStart-1)+3);
$sheet ->mergeCells('A'.($rowStart-1).':A'.(($rowStart-1)+2));


    $sheet->setCellValue('B1', "ФИЛЬТРЫ: ");
    $sheet->getStyle('B1')->getFont()->applyFromArray( [ 'name' => 'Arial', 'bold' => TRUE, 'italic' => FALSE, 'strikethrough' => FALSE, 'color' => [ 'rgb' => '808080' ] ] );
    $sheet->getStyle('B')->getAlignment()->setWrapText(true);
    $sheet->getStyle('B1')->getAlignment()->setWrapText(true);

    $sheet->setCellValue('C1', $textFilters);
    $sheet->mergeCells('C1:'.$arrColExccel[$j-1].'1');

    $sheet->setCellValue('B'.($rowStart-1),"План годовой");
    $sheet->getStyle('B'.($rowStart-1))->getFont()->applyFromArray( [ 'name' => 'Arial', 'bold' => TRUE, 'italic' => FALSE, 'strikethrough' => FALSE, 'color' => [ 'rgb' => '808080' ] ] );
    $sheet->getStyle('B')->getAlignment()->setWrapText(true);
    $sheet->getStyle('B'.($rowStart-1))->getAlignment()->setWrapText(true);

    $sheet->mergeCells('B'.($rowStart-1).':H'.($rowStart-1));

    $sheet->setCellValue('B'.($rowStart),"План");
    $sheet->getStyle('B'.($rowStart))->getFont()->applyFromArray( [ 'name' => 'Arial', 'bold' => TRUE, 'italic' => FALSE, 'strikethrough' => FALSE, 'color' => [ 'rgb' => '808080' ] ] );
    $sheet->getStyle('B')->getAlignment()->setWrapText(true);
    $sheet->getStyle('B'.($rowStart))->getAlignment()->setWrapText(true);

    $sheet->mergeCells('B'.($rowStart).':C'.($rowStart));

    $sheet->setCellValue('D'.($rowStart),"Фактическое выполнение");
    $sheet->getStyle('D'.($rowStart))->getFont()->applyFromArray( [ 'name' => 'Arial', 'bold' => TRUE, 'italic' => FALSE, 'strikethrough' => FALSE, 'color' => [ 'rgb' => '808080' ] ] );
    $sheet->getStyle('D')->getAlignment()->setWrapText(true);
    $sheet->getStyle('D'.($rowStart))->getAlignment()->setWrapText(true);

    $sheet->mergeCells('D'.($rowStart).':F'.($rowStart));


    $sheet->setCellValue('G'.($rowStart),"Задатки");
    $sheet->getStyle('G'.($rowStart))->getFont()->applyFromArray( [ 'name' => 'Arial', 'bold' => TRUE, 'italic' => FALSE, 'strikethrough' => FALSE, 'color' => [ 'rgb' => '808080' ] ] );
    $sheet->getStyle('G')->getAlignment()->setWrapText(true);
    $sheet->getStyle('G'.($rowStart))->getAlignment()->setWrapText(true);
    $sheet->mergeCells('G'.($rowStart).':H'.($rowStart));

    $col = 8;
    foreach($dates['months'] as $month){

      $name = $mounthArrray[(int)$month];
      $sheet->setCellValue($arrColExccel[$col].($rowStart-1),$name);
      $sheet->getStyle($arrColExccel[$col].($rowStart-1))->getFont()->applyFromArray( [ 'name' => 'Arial', 'bold' => TRUE, 'italic' => FALSE, 'strikethrough' => FALSE, 'color' => [ 'rgb' => '808080' ] ] );
      $sheet->getStyle($arrColExccel[$col])->getAlignment()->setWrapText(true);
      $sheet->getStyle($arrColExccel[$col].($rowStart-1))->getAlignment()->setWrapText(true);
      //echo $arrColExccel[$col+6]."\n";
      $sheet->mergeCells($arrColExccel[$col].($rowStart-1).':'.$arrColExccel[$col+6].($rowStart-1));

      $sheet->setCellValue($arrColExccel[$col].($rowStart),"План");
      $sheet->getStyle($arrColExccel[$col].($rowStart))->getFont()->applyFromArray( [ 'name' => 'Arial', 'bold' => TRUE, 'italic' => FALSE, 'strikethrough' => FALSE, 'color' => [ 'rgb' => '808080' ] ] );
      $sheet->getStyle($arrColExccel[$col])->getAlignment()->setWrapText(true);
      $sheet->getStyle($arrColExccel[$col].($rowStart))->getAlignment()->setWrapText(true);
      $sheet->mergeCells($arrColExccel[$col].($rowStart).':'.$arrColExccel[$col+1].($rowStart));

      $sheet->setCellValue($arrColExccel[$col+2].($rowStart),"Фактическое выполнение");
      $sheet->getStyle($arrColExccel[$col+2].($rowStart))->getFont()->applyFromArray( [ 'name' => 'Arial', 'bold' => TRUE, 'italic' => FALSE, 'strikethrough' => FALSE, 'color' => [ 'rgb' => '808080' ] ] );
      $sheet->getStyle($arrColExccel[$col+2])->getAlignment()->setWrapText(true);
      $sheet->getStyle($arrColExccel[$col+2].($rowStart))->getAlignment()->setWrapText(true);

      $sheet->mergeCells($arrColExccel[$col+2].($rowStart).':'.$arrColExccel[$col+4].($rowStart));


      $sheet->setCellValue($arrColExccel[$col+5].($rowStart),"Задатки");
      $sheet->getStyle($arrColExccel[$col+5].($rowStart))->getFont()->applyFromArray( [ 'name' => 'Arial', 'bold' => TRUE, 'italic' => FALSE, 'strikethrough' => FALSE, 'color' => [ 'rgb' => '808080' ] ] );
      $sheet->getStyle($arrColExccel[$col+5])->getAlignment()->setWrapText(true);
      $sheet->getStyle($arrColExccel[$col+5].($rowStart))->getAlignment()->setWrapText(true);

      $sheet->mergeCells($arrColExccel[$col+5].($rowStart).':'.$arrColExccel[$col+6].($rowStart));
      $col = $col + 7;
    }

$rowStart++;
$styleArray = array(
           'borders' => array(
               'allBorders' => array(
                   'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                   'color' => array('rgb' => '808080'),
               ),
           ),
       );


foreach($arrayHeder as $h => $w){

    $sheet->setCellValue($h.$rowStart, $w['title']);
    $sheet->getColumnDimension($h)->setWidth($w['width']);
    $sheet->getStyle($h.$rowStart)->getFont()->applyFromArray( [ 'name' => 'Arial', 'bold' => TRUE, 'italic' => FALSE, 'strikethrough' => FALSE, 'color' => [ 'rgb' => '808080' ],  ] );

    $sheet->getStyle($h)->getAlignment()->setWrapText(true);
    $sheet->getStyle($h.$rowStart)->getAlignment()->setWrapText(true);
}

$sheet->setCellValue('A2', "Сотрудники");
$sheet->getStyle('A2')->getFont()->applyFromArray( [ 'name' => 'Arial', 'bold' => TRUE, 'italic' => FALSE, 'strikethrough' => FALSE, 'color' => [ 'rgb' => '808080' ] ] );
$sheet->getStyle('A2')->getAlignment()->setWrapText(true);

$sheet ->getStyle('A2:'.$arrColExccel[$j-1].($rowStart + count($rows)))->applyFromArray($styleArray);

$i = $rowStart+1;
$k = 1;


foreach($rows as $row){
   $sheet->setCellValue('A'.$i, $k);
   $j = 0;
   foreach($arrRows as $ar){
        if($ar != 'fio') $row[$ar] = str_replace(" ","",$row[$ar]);
        $sheet->setCellValue($arrColExccel[$j].$i, htmlspecialchars_decode($row[$ar]));

        if($ar != 'fio' && isset($itogRow[$ar])){
          $itogRow[$ar] += (int)str_replace(" ","",$row[$ar]);
        }

        $j++;
   }

   $i++;
   $k++;
}
//$rowStart = $i;
$rowItog = $rowStart + count($rows)+1;
$sheet->setCellValue('A'.$rowItog, "Итого");
$sheet->getStyle('A'.$rowItog)->getFont()->applyFromArray( [ 'name' => 'Arial', 'bold' => TRUE, 'italic' => FALSE, 'strikethrough' => FALSE, 'color' => [ 'rgb' => '808080' ] ] );
$sheet->getStyle('A'.$rowItog)->getAlignment()->setWrapText(true);
$j = 0;
if($itogRow['real_summa_commission'] > 0 && $itogRow['summa_commission'] == 0) {
$itogRow['real_pr'] = '100%';
} else if ($itogRow['real_summa_commission'] > 0 && $itogRow['summa_commission'] > 0) {
  $itogRow['real_pr'] = (int)($itogRow['real_summa_commission']/$itogRow['summa_commission']*100) .'%';
} else {
  $itogRow['real_pr'] = '0%';
}

foreach($dates['months'] as $month){
  if($itogRow['real_summa_commission_'.$month] > 0 && $itogRow['summa_commission_'.$month] == 0) {
    $itogRow['real_pr_'.$month] = '100%';
  } else if ($itogRow['real_summa_commission_'.$month] > 0 && $itogRow['summa_commission_'.$month] > 0) {
    $itogRow['real_pr_'.$month] = (int)($itogRow['real_summa_commission_'.$month]/$itogRow['summa_commission_'.$month]*100) .'%';
  } else {
    $itogRow['real_pr_'.$month] = '0%';
  }
}

foreach($arrRows as $ar){
  if(isset($itogRow[$ar])){
     $sheet->setCellValue($arrColExccel[$j].$rowItog, htmlspecialchars_decode($itogRow[$ar]));
   }
     $j++;
}

$sheet ->getStyle('A2:'.$arrColExccel[$j-1].$rowItog)->applyFromArray($styleArray);


$writer = new Xlsx($spreadsheet);
//echo $_SERVER['DOCUMENT_ROOT'].'server/excel';
if(!file_exists($_SERVER['DOCUMENT_ROOT'].'/server/excel')){
  mkdir($_SERVER['DOCUMENT_ROOT'].'/server/excel', 0777);
}

$name_file = '/server/excel/sales_plan_'.$date.'.xlsx';
$name = 'sales_plan_'.$date.'.xlsx';


$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save($_SERVER['DOCUMENT_ROOT'].$name_file);
$result['file'] = $name_file;
$result['result'] = 'ok';
$result['name'] = $name;
echo json_encode($result);