327 lines
11 KiB
PHP
327 lines
11 KiB
PHP
<?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 = json_decode($_POST['columns'], true);
|
||
$rows = json_decode($_POST['rows'], true);
|
||
//var_dump($columns);
|
||
|
||
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
||
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
|
||
|
||
$spreadsheet = new Spreadsheet();
|
||
$sheet = $spreadsheet->getActiveSheet();
|
||
//if($_POST['type'] == 'client'){
|
||
$sheet->setTitle('Статистика клиентов');
|
||
//}
|
||
if($_POST['type'] == 'object'){
|
||
$sheet->setTitle('Статистика объектов');
|
||
}
|
||
$arrColExccel = ['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','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ',
|
||
'BA','BB','BC','BD','BE','BF','BG','BH','BI','BJ','BK','BL','BM','BN','BO','BP','BQ','BR','BS','BT','BU','BV','BW','BX','BY','BZ'];
|
||
$columnStart = 'A';
|
||
$rowStart = 3;
|
||
$tasksId = false;
|
||
$closetId = false;
|
||
$tempRow = false;
|
||
foreach($columns as $keyCol => $col){
|
||
if($col['id'] == 'tasks' && $col['visible']){
|
||
|
||
if(!$tempRow){
|
||
$rowStart++;
|
||
$tempRow = true;
|
||
}
|
||
$tasksId = $keyCol;
|
||
}
|
||
|
||
if($col['id'] == 'closet' && $col['visible']){
|
||
if(!$tempRow){
|
||
$rowStart++;
|
||
$tempRow = true;
|
||
}
|
||
$closetId = $keyCol;
|
||
}
|
||
}
|
||
|
||
|
||
$arrayHeder = [$columnStart => ['title'=>'№','width'=>5]];
|
||
$arrRows = array();
|
||
$j = 0;
|
||
$arrPodTask = array();
|
||
$arrPodCloset = array();
|
||
foreach($columns as $col){
|
||
if($col['visible']){
|
||
|
||
if($col['id'] != 'tasks' && $col['id'] != 'closet'){
|
||
$tempArr['title'] = $col['text'];
|
||
if($col['id'] == 'fio'){
|
||
$tempArr['width'] = 35;
|
||
} else {
|
||
$tempArr['width'] = 17;
|
||
}
|
||
$arrayHeder[$arrColExccel[$j]] = $tempArr;
|
||
$arrRows[$arrColExccel[$j]] = $col['id'];
|
||
if($col['id'] == 'task' || $col['id'] == 'task_new' || $col['id'] == 'task_old' || $col['id'] == 'task_slung'){
|
||
$arrPodTask[] = $arrColExccel[$j];
|
||
}
|
||
if($col['podcheck'] == 'closet'){
|
||
$arrPodCloset[] = $arrColExccel[$j];
|
||
}
|
||
|
||
|
||
$j++;
|
||
}
|
||
}
|
||
}
|
||
|
||
$textFilters = '';
|
||
if(isset($_POST['period'])){
|
||
$period = $_POST['period'];
|
||
$textFilters .= "Период - ";
|
||
switch($period){
|
||
case 'today':{
|
||
$textFilters .= 'Сегодня';
|
||
break;
|
||
}
|
||
case 'week':{
|
||
$textFilters .= 'Неделя';
|
||
break;
|
||
}
|
||
case 'month':{
|
||
$textFilters .= 'Месяц';
|
||
break;
|
||
}
|
||
case 'quarter':{
|
||
$textFilters .= 'Квартал';
|
||
break;
|
||
}
|
||
case 'year':{
|
||
$textFilters .= 'Год';
|
||
break;
|
||
}
|
||
case 'all':{
|
||
$textFilters .= 'Все время';
|
||
break;
|
||
}
|
||
case 'dates':{
|
||
if(isset($_POST['onDate'])){
|
||
$textFilters .= 'C '.date('d.m.Y', strtotime($_POST['onDate']));
|
||
}
|
||
if(isset($_POST['offDate'])){
|
||
$textFilters .= ' по '.date('d.m.Y', strtotime($_POST['offDate']));
|
||
}
|
||
break;
|
||
}
|
||
}
|
||
}
|
||
if(isset($_POST['responsible'])){
|
||
$responsible = json_decode($_POST['responsible']);
|
||
|
||
if(!empty($responsible)){
|
||
if($textFilters != '') $textFilters .= "; ";
|
||
$textFilters .= " Ответственный - ";
|
||
foreach($responsible as $emp){
|
||
if(strpos($emp, 'allagency_') !== false){
|
||
$textFilters .= ' все агенство, ';
|
||
} else if (strpos($emp, 'all_') !== false){
|
||
$user_id = str_replace('all_', '', $emp);
|
||
$textFilters .= ' вся группа менеджера ';
|
||
$sql = "SELECT first_name, last_name, middle_name FROM users WHERE id=".$user_id;
|
||
$q = mysql_query($sql);
|
||
$r = mysql_fetch_assoc($q);
|
||
$textFilters .= trim($r['last_name'] . ' ' . $r['first_name'] . ' ' . $r['middle_name']).', ';
|
||
} else {
|
||
$sql = "SELECT first_name, last_name, middle_name, manager FROM users WHERE id=".$emp;
|
||
//echo $sql;
|
||
$post = ' агент ';
|
||
$q = mysql_query($sql);
|
||
$r = mysql_fetch_assoc($q);
|
||
if($r['manager'] == 1){
|
||
$post = ' менеджер ';
|
||
}
|
||
$textFilters .= $post.' '.trim($r['last_name'] . ' ' . $r['first_name'] . ' ' . $r['middle_name']).', ';
|
||
}
|
||
}
|
||
$textFilters = mb_substr($textFilters,0,-2);
|
||
|
||
}
|
||
|
||
|
||
|
||
}
|
||
|
||
if(isset($_POST['charts_type'])){
|
||
$charts_type = json_decode($_POST['charts_type']);
|
||
if(!empty($charts_type)){
|
||
if($textFilters != '') $textFilters .= "; ";
|
||
$textFilters .= " ТИП: ";
|
||
foreach($charts_type as $chart){
|
||
$textFilters .= ' '.$chart.', ';
|
||
}
|
||
$textFilters = mb_substr($textFilters,0,-2);
|
||
}
|
||
|
||
}
|
||
|
||
|
||
if(isset($_POST['charts_objects_type'])){
|
||
$charts_objects_type = json_decode($_POST['charts_objects_type']);
|
||
if(!empty($charts_objects_type)){
|
||
if($textFilters != '') $textFilters .= "; ";
|
||
$textFilters .= " Тип недвижимости - ";
|
||
foreach($charts_objects_type as $object){
|
||
$textFilters .= ' '.str_replace(['к_кв','__', '_'],['к.кв.', ' ',' '],$object).', ';
|
||
}
|
||
$textFilters = mb_substr($textFilters,0,-2);
|
||
}
|
||
|
||
}
|
||
|
||
if(isset($_POST['charts_rol'])){
|
||
$charts_rol = json_decode($_POST['charts_rol']);
|
||
if(!empty($charts_rol)){
|
||
if($textFilters != '') $textFilters .= "; ";
|
||
$textFilters .= " Роль клиента - ";
|
||
foreach($charts_rol as $rol){
|
||
$textFilters .= ' '.$rol.', ';
|
||
}
|
||
$textFilters = mb_substr($textFilters,0,-2);
|
||
}
|
||
|
||
}
|
||
|
||
if(isset($_POST['source'])){
|
||
$sources = json_decode($_POST['source']);
|
||
if(!empty($sources)){
|
||
if($textFilters != '') $textFilters .= "; ";
|
||
$textFilters .= " Источник - ";
|
||
$sql = "SELECT source FROM advertising_sources WHERE id in (".implode(',', $sources).")";
|
||
|
||
$q = mysql_query($sql);
|
||
while($r = mysql_fetch_assoc($q)){
|
||
$textFilters .= $r['source'].', ';
|
||
}
|
||
$textFilters = mb_substr($textFilters,0,-2);
|
||
}
|
||
|
||
}
|
||
|
||
|
||
if(isset($_POST['funnel'])){
|
||
$funnels = json_decode($_POST['funnel']);
|
||
if(!empty($funnels)){
|
||
if($textFilters != '') $textFilters .= "; ";
|
||
$textFilters .= " Воронки - ";
|
||
$sql = "SELECT id, name FROM funnel WHERE id in (".implode(',', $funnels).")";
|
||
|
||
$q = mysql_query($sql);
|
||
while($r = mysql_fetch_assoc($q)){
|
||
$textFilters .= $r['name'].', ';
|
||
}
|
||
$textFilters = mb_substr($textFilters,0,-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');
|
||
|
||
|
||
if($tasksId){
|
||
$sheet->setCellValue($arrPodTask[0].($rowStart-1), $columns[$tasksId]['text']);
|
||
$sheet->getStyle($arrPodTask[0].($rowStart-1))->getFont()->applyFromArray( [ 'name' => 'Arial', 'bold' => TRUE, 'italic' => FALSE, 'strikethrough' => FALSE, 'color' => [ 'rgb' => '808080' ] ] );
|
||
$sheet->getStyle($arrPodTask[0])->getAlignment()->setWrapText(true);
|
||
$sheet->getStyle($arrPodTask[0].($rowStart-1))->getAlignment()->setWrapText(true);
|
||
|
||
$sheet->mergeCells($arrPodTask[0].($rowStart-1).':'.$arrPodTask[count($arrPodTask)-1].($rowStart-1));
|
||
|
||
}
|
||
|
||
if($closetId){
|
||
$sheet->setCellValue($arrPodCloset[0].($rowStart-1), $columns[$closetId]['text']);
|
||
$sheet->getStyle($arrPodCloset[0].($rowStart-1))->getFont()->applyFromArray( [ 'name' => 'Arial', 'bold' => TRUE, 'italic' => FALSE, 'strikethrough' => FALSE, 'color' => [ 'rgb' => '808080' ] ] );
|
||
$sheet->getStyle($arrPodCloset[0])->getAlignment()->setWrapText(true);
|
||
$sheet->getStyle($arrPodCloset[0].($rowStart-1))->getAlignment()->setWrapText(true);
|
||
|
||
$sheet->mergeCells($arrPodCloset[0].($rowStart-1).':'.$arrPodCloset[count($arrPodCloset)-1].($rowStart-1));
|
||
|
||
}
|
||
|
||
$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 ->getStyle('A'.($rowStart-1).':'.$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 == 'task'){
|
||
$val = (int)$row['task_new'] + (int)$row['task_old'] + (int)$row['task_slung'];
|
||
$sheet->setCellValue($arrColExccel[$j].$i, $val);
|
||
} else if($ar == 'summa'){
|
||
$val = $row['sdelka'].'/'.$row['summa'];
|
||
$sheet->setCellValue($arrColExccel[$j].$i, $val);
|
||
}
|
||
else{
|
||
$sheet->setCellValue($arrColExccel[$j].$i, $row[$ar]);
|
||
}
|
||
|
||
$j++;
|
||
}
|
||
|
||
$i++;
|
||
$k++;
|
||
}
|
||
|
||
|
||
$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/statistics_clients_'.$date.'.xlsx';
|
||
$name = 'statistics_clients_'.$date.'.xlsx';
|
||
if($_POST['type'] == 'object'){
|
||
$name_file = '/server/excel/statistics_object_'.$date.'.xlsx';
|
||
$name = 'statistics_object_'.$date.'.xlsx';
|
||
}
|
||
|
||
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
|
||
$writer->save($_SERVER['DOCUMENT_ROOT'].$name_file);
|
||
$res['file'] = $name_file;
|
||
$res['result'] = 'ok';
|
||
$res['name'] = $name;
|
||
echo json_encode($res);
|