1524 lines
55 KiB
PHP
1524 lines
55 KiB
PHP
|
<?php
|
|||
|
|
require_once($_SERVER['DOCUMENT_ROOT']."/config.php");
|
|||
|
|
function deClearInputElements($array){
|
|||
|
|
$res = array();
|
|||
|
|
if(!empty($array)){
|
|||
|
|
foreach ($array as $key => $value){
|
|||
|
|
$res[$key] = str_replace("\n", " ", htmlspecialchars_decode($value));
|
|||
|
|
$res[$key] = str_replace("\\", "/", htmlspecialchars_decode($value));
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
return $res;
|
|||
|
|
}
|
|||
|
|
require_once($_SERVER['DOCUMENT_ROOT'].'/vendor/autoload.php');
|
|||
|
|
set_time_limit(0);
|
|||
|
|
ini_set('memory_limit', '8192M');
|
|||
|
|
|
|||
|
|
// hide_client_contacts: маска fio/phone/email клиента в Excel-выгрузке заявок.
|
|||
|
|
$hideContactsExcel = function_exists('should_hide_contacts_for_current_user')
|
|||
|
|
&& should_hide_contacts_for_current_user();
|
|||
|
|
/*error_reporting(E_ALL | E_STRICT);
|
|||
|
|
ini_set('display_errors', 1);*/
|
|||
|
|
$data = json_decode(file_get_contents("php://input"));
|
|||
|
|
//var_dump($data);
|
|||
|
|
$user_id = $data->user_id;
|
|||
|
|
$agency_id = $data->agency_id;
|
|||
|
|
$columns = json_decode(json_encode($data->columns, JSON_UNESCAPED_UNICODE),true);
|
|||
|
|
$columnsCastom = array();
|
|||
|
|
$columnsFunnels = array();
|
|||
|
|
$page = 1;
|
|||
|
|
$page = (int)$data->page;
|
|||
|
|
if($page == 0) $page = 1;
|
|||
|
|
$type = $data->type;
|
|||
|
|
$limit = $data->limit;
|
|||
|
|
$date = date('d.m.Y');
|
|||
|
|
if((int)$agency_id == 0 || (int)$user_id==0){
|
|||
|
|
$res['cont'] = false;
|
|||
|
|
$res['error'] = "Не заданы пользователи";
|
|||
|
|
echo json_encode($res);
|
|||
|
|
}
|
|||
|
|
$raport_id = 0;
|
|||
|
|
if(isset($data->raport_id)){
|
|||
|
|
$raport_id = (int)$data->raport_id;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$filter = [];
|
|||
|
|
if(isset($data->filter)){
|
|||
|
|
$filter = $data->filter;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//var_dump($filter);
|
|||
|
|
|
|||
|
|
$pdo = new MysqlPdo(hst, ndb, user, pass);
|
|||
|
|
$sql = "SET NAMES 'utf8'";
|
|||
|
|
$pdo->query($sql);
|
|||
|
|
|
|||
|
|
//$db_sphinx = new MysqlPdo(hstsph2, null, null, null, true, '9306');
|
|||
|
|
|
|||
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
|||
|
|
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
|
|||
|
|
|
|||
|
|
|
|||
|
|
$spreadsheet = new Spreadsheet();
|
|||
|
|
//$sheet = $spreadsheet->getActiveSheet();
|
|||
|
|
|
|||
|
|
|
|||
|
|
//echo $_SERVER['DOCUMENT_ROOT'].'server/excel';
|
|||
|
|
if(!file_exists($_SERVER['DOCUMENT_ROOT'].'/server/excel')){
|
|||
|
|
mkdir($_SERVER['DOCUMENT_ROOT'].'/server/excel', 0777);
|
|||
|
|
}
|
|||
|
|
if(!file_exists($_SERVER['DOCUMENT_ROOT'].'/server/excel/'.$user_id)){
|
|||
|
|
mkdir($_SERVER['DOCUMENT_ROOT'].'/server/excel/'.$user_id, 0777);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$name_file = '/server/excel/'.$user_id.'/raport_'.$date.'.xlsx';
|
|||
|
|
$name = 'raport_'.$date.'.xlsx';
|
|||
|
|
$name_file_json = '/server/excel/'.$user_id.'/raport_'.$date.'.json';
|
|||
|
|
$name_file_main_json = $_SERVER['DOCUMENT_ROOT'] .'/server/excel/'.$user_id.'/raport_main_'.$date;
|
|||
|
|
$name_file_main_setting = $_SERVER['DOCUMENT_ROOT'] . '/server/excel/'.$user_id.'/raport_setting_'.$date.'.json';
|
|||
|
|
|
|||
|
|
//if($_SESSION['id'] != 8826){
|
|||
|
|
|
|||
|
|
/*if($page == 1){
|
|||
|
|
|
|||
|
|
|
|||
|
|
// $writer = new Xlsx($spreadsheet);
|
|||
|
|
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
|
|||
|
|
|
|||
|
|
|
|||
|
|
} else {
|
|||
|
|
//$writer = new Xlsx($spreadsheet);
|
|||
|
|
$typef = \PhpOffice\PhpSpreadsheet\IOFactory::identify($_SERVER['DOCUMENT_ROOT'].$name_file);
|
|||
|
|
$objReader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($typef);
|
|||
|
|
|
|||
|
|
$spreadsheet = $objReader->load($_SERVER['DOCUMENT_ROOT'].$name_file);
|
|||
|
|
// $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($_SERVER['DOCUMENT_ROOT'].$name_file);
|
|||
|
|
$writer = new Xlsx($spreadsheet);
|
|||
|
|
}*/
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
$ec = new ExportClass();
|
|||
|
|
$data = (object)array('user_id'=>$user_id, 'type'=>$type, 'raport_id'=>$raport_id);
|
|||
|
|
$columnsTemp = $ec->getSettingsExport($data);
|
|||
|
|
//var_dump($columnsTemp);
|
|||
|
|
$ec->set_agency($agency_id);
|
|||
|
|
|
|||
|
|
$users = $ec->get_all_users();
|
|||
|
|
|
|||
|
|
$ec->close();
|
|||
|
|
$clientsIds = array();
|
|||
|
|
$clients = array();
|
|||
|
|
$reqs = array();
|
|||
|
|
$reqIds = array();
|
|||
|
|
$arrayJson = array();
|
|||
|
|
$reqTypes = array();
|
|||
|
|
$funnels = array();
|
|||
|
|
$funnelsIds = array();
|
|||
|
|
$arrEtap = [1=>'Новый клиент', 2=>'В работе', 3=>'Презентация', 4=>'Показ', 5=>'Бронь', 6=>'Подаем на ипотеку', 7=>'Сделка', 8=>'Клиент закрыт'];
|
|||
|
|
$steps = array();
|
|||
|
|
$sources = array();
|
|||
|
|
$partners = array();
|
|||
|
|
$denials = array();
|
|||
|
|
$castom_data = array();
|
|||
|
|
$rez = array();
|
|||
|
|
$objectIds = array();
|
|||
|
|
$objects = array();
|
|||
|
|
$contracts = array();
|
|||
|
|
|
|||
|
|
|
|||
|
|
if(!empty($columnsTemp['fields_fixes'])){
|
|||
|
|
$columns = $columnsTemp['fields_fixes'];
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if(!empty($columnsTemp['fields_castom'])){
|
|||
|
|
$columnsCastom = $columnsTemp['fields_castom'];
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if(!empty($columnsTemp['fields_funnels'])){
|
|||
|
|
$columnsFunnels = $columnsTemp['fields_funnels'];
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$nameRaport = 'Отчет по заявкам';
|
|||
|
|
if(!empty($columnsTemp['title_raport'])){
|
|||
|
|
$nameRaport = str_replace('/','_',$columnsTemp['title_raport']);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//Список воронок
|
|||
|
|
$sql_funnels = "SELECT * FROM funnel WHERE agency_id = {$agency_id}";
|
|||
|
|
$q_funnels = $pdo->query($sql_funnels);
|
|||
|
|
while($r_funnels = $pdo->fetch_assoc($q_funnels)){
|
|||
|
|
$funnels[$r_funnels['id']] = $r_funnels;
|
|||
|
|
$funnelsIds[] = $r_funnels['id'];
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//Список этапов
|
|||
|
|
if(!empty($funnelsIds)){
|
|||
|
|
$sql_s_n = "SELECT name, funnel_id, id, next_step, pole_end_list FROM funnel_steps WHERE funnel_id in (".implode(',',$funnelsIds).")";
|
|||
|
|
|
|||
|
|
$q_s_n = $pdo->query($sql_s_n);
|
|||
|
|
while($r_s_n = $pdo->fetch_assoc($q_s_n)){
|
|||
|
|
$r_s_n['fields'] = json_decode(htmlspecialchars_decode($r_s_n['pole_end_list']), true);
|
|||
|
|
$steps[$r_s_n['id']] = $r_s_n;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$rowsColumnsName = array();
|
|||
|
|
|
|||
|
|
$arrColExcel = ['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'];
|
|||
|
|
$namesTitle = $ec->get_columns_title();
|
|||
|
|
$lit1 = 0;
|
|||
|
|
$lit2 = -1;
|
|||
|
|
$arrayHeder = array();
|
|||
|
|
|
|||
|
|
$arrayHeder2 = array();
|
|||
|
|
foreach($columns as $key => $column){
|
|||
|
|
|
|||
|
|
if($column == 1){
|
|||
|
|
if(($key == 'denial' || $key == 'denial_comm') && ($filter->stages->work == 1 || $filter->stages->hit == 1) && $raport_id == 0){
|
|||
|
|
|
|||
|
|
} else if ($key == 'summa' && $filter->stages->hit == 0 && $raport_id == 0) {
|
|||
|
|
|
|||
|
|
} else {
|
|||
|
|
$nameCol = $key;
|
|||
|
|
$width = 35;
|
|||
|
|
if($key == 'desc'){
|
|||
|
|
$width = 100;
|
|||
|
|
}
|
|||
|
|
if($key == 'date' || $key == 'id'){
|
|||
|
|
$width = 15;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
$title = $namesTitle[$key];
|
|||
|
|
$lit = '';
|
|||
|
|
$lit .= $arrColExcel[$lit1];
|
|||
|
|
if($lit2 != -1){
|
|||
|
|
$lit .= $arrColExcel[$lit2];
|
|||
|
|
}
|
|||
|
|
$arrayHeder[$lit] = array(
|
|||
|
|
'name' => $nameCol,
|
|||
|
|
'title' => $title,
|
|||
|
|
'width' => $width,
|
|||
|
|
'key' => $key,
|
|||
|
|
|
|||
|
|
|
|||
|
|
);
|
|||
|
|
if($raport_id > 0){
|
|||
|
|
$arrayHeder[$lit]['rowspan'] = 2;
|
|||
|
|
}
|
|||
|
|
$rowsColumnsName[$key] = $lit;
|
|||
|
|
$lit1++;
|
|||
|
|
if(!isset($arrColExcel[$lit1])) {
|
|||
|
|
$lit2++;
|
|||
|
|
$lit1 = 0;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//var_dump($columnsCastom);
|
|||
|
|
|
|||
|
|
if(!empty($columnsCastom)){
|
|||
|
|
foreach($columnsCastom as $key => $column){
|
|||
|
|
// echo $raport_id."\n";
|
|||
|
|
|
|||
|
|
if($column['val'] == 1){
|
|||
|
|
$nameCol = $key;
|
|||
|
|
$width = 25;
|
|||
|
|
|
|||
|
|
$title = $column['title'];
|
|||
|
|
$lit = '';
|
|||
|
|
|
|||
|
|
if($lit2 != -1){
|
|||
|
|
$lit .= $arrColExcel[$lit2];
|
|||
|
|
}
|
|||
|
|
$lit .= $arrColExcel[$lit1];
|
|||
|
|
//echo $lit." ";
|
|||
|
|
$arrayHeder[$lit] = array(
|
|||
|
|
'name' => $nameCol,
|
|||
|
|
'title' => $title,
|
|||
|
|
'width' => $width,
|
|||
|
|
'key' => $key,
|
|||
|
|
|
|||
|
|
|
|||
|
|
);
|
|||
|
|
if($raport_id > 0){
|
|||
|
|
$arrayHeder[$lit]['rowspan'] = 2;
|
|||
|
|
}
|
|||
|
|
$rowsColumnsName[$key] = $lit;
|
|||
|
|
$lit1++;
|
|||
|
|
|
|||
|
|
if(!isset($arrColExcel[$lit1])) {
|
|||
|
|
$lit2++;
|
|||
|
|
|
|||
|
|
$lit1 = 0;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$funnelFieldsIds = [];
|
|||
|
|
if($raport_id > 0 && !empty($columnsFunnels)){
|
|||
|
|
// $columnsFunnels
|
|||
|
|
foreach ($columnsFunnels as $key_cf => $cf){
|
|||
|
|
// var_dump($cf);
|
|||
|
|
if($cf['is_active'] == 1 && isset($funnels[$key_cf])){
|
|||
|
|
$funnelFieldsIds[] = $key_cf;
|
|||
|
|
foreach($cf['steps'] as $key_st => $st){
|
|||
|
|
// if($st['is_active'] == 1){
|
|||
|
|
$nameCol = $key_st;
|
|||
|
|
$width = 15;
|
|||
|
|
$colspan = 1;
|
|||
|
|
$title = $steps[str_replace('step','',$key_st)]['name'];
|
|||
|
|
$lit = '';
|
|||
|
|
$litStep = '';
|
|||
|
|
|
|||
|
|
if($lit2 != -1){
|
|||
|
|
$lit .= $arrColExcel[$lit2];
|
|||
|
|
}
|
|||
|
|
$lit .= $arrColExcel[$lit1];
|
|||
|
|
$litStep = $lit;
|
|||
|
|
//echo $lit." ";
|
|||
|
|
$step_visible = false;
|
|||
|
|
|
|||
|
|
/*$arrayHeder[$lit] = array(
|
|||
|
|
'name' => $nameCol,
|
|||
|
|
'title' => $title,
|
|||
|
|
'width' => $width*($colspan+1),
|
|||
|
|
'colspan'=> $colspan,
|
|||
|
|
'lit2'=>str_replace('step','step_2',$key_st),
|
|||
|
|
|
|||
|
|
);*/
|
|||
|
|
if($st['is_active'] == 1){
|
|||
|
|
$step_visible = true;
|
|||
|
|
$arrayHeder2[$lit] = ['title'=>'Дата', 'width'=>15, 'row'=>1];
|
|||
|
|
|
|||
|
|
if($colspan > 0){
|
|||
|
|
for($i=0; $i<$colspan; $i++){
|
|||
|
|
$lit1++;
|
|||
|
|
|
|||
|
|
if(!isset($arrColExcel[$lit1])) {
|
|||
|
|
$lit2++;
|
|||
|
|
|
|||
|
|
$lit1 = 0;
|
|||
|
|
}
|
|||
|
|
$lit = '';
|
|||
|
|
if($lit2 != -1){
|
|||
|
|
$lit .= $arrColExcel[$lit2];
|
|||
|
|
}
|
|||
|
|
$lit .= $arrColExcel[$lit1];
|
|||
|
|
if($i==0){
|
|||
|
|
$name2 = 'Ответственные';
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$arrayHeder2[$lit] = ['title'=>$name2, 'width'=>15, 'row'=>1];
|
|||
|
|
$rowsColumnsName[str_replace('step','step_2',$key_st)] = $lit;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
$lit = '';
|
|||
|
|
if($lit2 != -1){
|
|||
|
|
$lit .= $arrColExcel[$lit2];
|
|||
|
|
}
|
|||
|
|
$lit .= $arrColExcel[$lit1];
|
|||
|
|
//$rowsColumnsName[str_replace('step','step_2',$key_st)] = $lit;
|
|||
|
|
}
|
|||
|
|
$colspan++;
|
|||
|
|
$lit1++;
|
|||
|
|
|
|||
|
|
if(!isset($arrColExcel[$lit1])) {
|
|||
|
|
$lit2++;
|
|||
|
|
|
|||
|
|
$lit1 = 0;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
/* $lit1++;
|
|||
|
|
|
|||
|
|
if(!isset($arrColExcel[$lit1])) {
|
|||
|
|
$lit2++;
|
|||
|
|
|
|||
|
|
$lit1 = 0;
|
|||
|
|
}*/
|
|||
|
|
if(isset($st['fields'])){
|
|||
|
|
foreach($st['fields'] as $key_f => $sf){
|
|||
|
|
if($sf['is_active'] == 1 && isset($steps[str_replace('step', '', $key_st)]['fields'][$key_f])){
|
|||
|
|
$step_visible = true;
|
|||
|
|
$lit = '';
|
|||
|
|
if($lit2 != -1){
|
|||
|
|
$lit .= $arrColExcel[$lit2];
|
|||
|
|
}
|
|||
|
|
$lit .= $arrColExcel[$lit1];
|
|||
|
|
$name3 = $steps[str_replace('step', '', $key_st)]['fields'][$key_f]['name'];
|
|||
|
|
$arrayHeder2[$lit] = ['title'=>$name3, 'width'=>15, 'row'=>1];
|
|||
|
|
$rowsColumnsName[$key_st.'field'.$key_f] = $lit;
|
|||
|
|
// $rowsColumnsName[str_replace('step','step_2',$key_st)] = $lit;
|
|||
|
|
|
|||
|
|
// $row_header2[] = array('name'=>$key_st.'field'.$key_f, 'title'=>$name3);
|
|||
|
|
$colspan++;
|
|||
|
|
$lit1++;
|
|||
|
|
|
|||
|
|
if(!isset($arrColExcel[$lit1])) {
|
|||
|
|
$lit2++;
|
|||
|
|
|
|||
|
|
$lit1 = 0;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
if($step_visible){
|
|||
|
|
$rowsColumnsName[$key_st] = $litStep;
|
|||
|
|
$arrayHeder[$litStep] = array(
|
|||
|
|
'name' => $nameCol,
|
|||
|
|
'title' => $title,
|
|||
|
|
'width' => $width*($colspan+1),
|
|||
|
|
'colspan'=> $colspan,
|
|||
|
|
'lit2'=>str_replace('step','step_2',$key_st),
|
|||
|
|
|
|||
|
|
);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// }
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
//var_dump($rowsColumnsName);
|
|||
|
|
|
|||
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|||
|
|
|
|||
|
|
$sheet->setTitle($nameRaport);
|
|||
|
|
$styleArray = array(
|
|||
|
|
'borders' => array(
|
|||
|
|
'allBorders' => array(
|
|||
|
|
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
|
|||
|
|
'color' => array('rgb' => '808080'),
|
|||
|
|
),
|
|||
|
|
),
|
|||
|
|
);
|
|||
|
|
//var_dump($arrayHeder);
|
|||
|
|
|
|||
|
|
if($page == 1){
|
|||
|
|
foreach($arrayHeder as $h => $w){
|
|||
|
|
$rw = 1;
|
|||
|
|
if(isset($w['row'])){
|
|||
|
|
$rw = $rowStart + (int)$w['row'];
|
|||
|
|
}
|
|||
|
|
$sheet->setCellValue($h.$rw, $w['title']);
|
|||
|
|
// $sheet->getColumnDimension($h)->setWidth($w['width']);
|
|||
|
|
$sheet->getStyle($h.'1')->getFont()->applyFromArray( [ 'name' => 'Arial', 'bold' => TRUE, 'italic' => FALSE, 'strikethrough' => FALSE, 'color' => [ 'rgb' => '808080' ], ] );
|
|||
|
|
|
|||
|
|
$sheet->getStyle($h.$rw)->getFont()->applyFromArray( [ 'name' => 'Arial', 'bold' => TRUE, 'italic' => FALSE, 'strikethrough' => FALSE, 'color' => [ 'rgb' => '808080' ], ] );
|
|||
|
|
|
|||
|
|
/* $sheet->getStyle($h)->getAlignment()->setWrapText(true);
|
|||
|
|
$sheet->getStyle($h.$rw)->getAlignment()->setWrapText(true);
|
|||
|
|
$sheet->getStyle($h.'1')->getAlignment()->setWrapText(true);*/
|
|||
|
|
if(isset($w['rowspan'])){
|
|||
|
|
$sheet->mergeCells($h.$rw.':'.$h.($rw+1));
|
|||
|
|
}
|
|||
|
|
if(isset($w['colspan'])){
|
|||
|
|
$in = $rowsColumnsName[$w['name']];
|
|||
|
|
if(isset($rowsColumnsName[$w['lit2']])){
|
|||
|
|
|
|||
|
|
$in2 = $rowsColumnsName[$w['lit2']];
|
|||
|
|
// echo $in."\n";
|
|||
|
|
//echo $in2."\n";
|
|||
|
|
if($in !== false){
|
|||
|
|
//$index = $in + (int)$w['colspan'];
|
|||
|
|
//$h2 = $arrColExccel[$index];
|
|||
|
|
$sheet->mergeCells($in.'1'.':'.$in2.'1');
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
$sheet->getColumnDimension($h)->setAutoSize(true);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if(!empty($arrayHeder2)){
|
|||
|
|
foreach($arrayHeder2 as $h => $w){
|
|||
|
|
$rw = 2;
|
|||
|
|
/* if(isset($w['row'])){
|
|||
|
|
$rw = $rowStart + (int)$w['row'];
|
|||
|
|
}*/
|
|||
|
|
$sheet->setCellValue($h.$rw, $w['title']);
|
|||
|
|
// $sheet->getColumnDimension($h)->setWidth($w['width']);
|
|||
|
|
$sheet->getStyle($h.$rw)->getFont()->applyFromArray( [ 'name' => 'Arial', 'bold' => TRUE, 'italic' => FALSE, 'strikethrough' => FALSE, 'color' => [ 'rgb' => '808080' ], ] );
|
|||
|
|
|
|||
|
|
/* $sheet->getStyle($h)->getAlignment()->setWrapText(true);
|
|||
|
|
$sheet->getStyle($h.$rw)->getAlignment()->setWrapText(true);*/
|
|||
|
|
$sheet->getColumnDimension($h)->setAutoSize(true);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$cont = true;
|
|||
|
|
//echo $page."/n";
|
|||
|
|
if($page == 1){
|
|||
|
|
//Сбор данных
|
|||
|
|
$sql = "SELECT * FROM requisitions WHERE 1 AND (user_id in (select id from users where id=$user_id or id_manager=$user_id or id_manager in
|
|||
|
|
(select id from users where id_manager=$user_id)) or who_work in (select id from users where id=$user_id or id_manager=$user_id or id_manager in
|
|||
|
|
(select id from users where id_manager=$user_id))) ";
|
|||
|
|
if (isset($_SESSION['where_req_campaigns'])) {
|
|||
|
|
$where = $_SESSION['where_req_campaigns'];
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$sql = $where;
|
|||
|
|
|
|||
|
|
if($raport_id > 0 && !empty($funnelFieldsIds)){
|
|||
|
|
$sql .= " AND funnel_id in (".implode(',',$funnelFieldsIds).") ";
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$q = $pdo->query($sql);
|
|||
|
|
|
|||
|
|
$rows = $pdo->num_rows($q);
|
|||
|
|
$allPages = ceil($rows/$limit);
|
|||
|
|
if($page == $allPages){
|
|||
|
|
$cont = false;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$sql_res = $sql;
|
|||
|
|
if(isset($_SESSION['where_req_campaigns_order'])){
|
|||
|
|
$sql_res .= $_SESSION['where_req_campaigns_order'];
|
|||
|
|
}
|
|||
|
|
/* if(isset($_SESSION['where_req'])){
|
|||
|
|
$sql_res = $_SESSION['where_req'];
|
|||
|
|
} else {
|
|||
|
|
if($raport_id > 0){
|
|||
|
|
$sql_res = $sql." order by funnel_id, created_at desc ";
|
|||
|
|
} else {
|
|||
|
|
$sql_res = $sql." order by created_at desc ";
|
|||
|
|
}
|
|||
|
|
}*/
|
|||
|
|
$q_res = $pdo->query($sql_res);
|
|||
|
|
$i = 0;
|
|||
|
|
while($r = $pdo->fetch_assoc($q_res)){
|
|||
|
|
$arrayJson['reqs_ids'][] = (int)$r['id'];
|
|||
|
|
if($i < $limit){
|
|||
|
|
$reqs[] = $r;
|
|||
|
|
$clientsIds[] = (int)$r['client_id'];
|
|||
|
|
$reqIds[] = (int)$r['id'];
|
|||
|
|
if((int)$r['object_id'] > 0){
|
|||
|
|
$objectIds[] = (int)$r['object_id'];
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
$i++;
|
|||
|
|
}
|
|||
|
|
$arrayJson['pages'] = $allPages;
|
|||
|
|
$arrayJson['rows'] = $rows;
|
|||
|
|
$json = json_encode($arrayJson);
|
|||
|
|
if(file_exists($_SERVER['DOCUMENT_ROOT'].'/'.$name_file_json)){
|
|||
|
|
unlink($_SERVER['DOCUMENT_ROOT'].'/'.$name_file_json);
|
|||
|
|
}
|
|||
|
|
file_put_contents($_SERVER['DOCUMENT_ROOT'].'/'.$name_file_json, $json, FILE_APPEND);
|
|||
|
|
if($page == 1){
|
|||
|
|
$setting = array();
|
|||
|
|
if(file_exists($name_file_main_setting)){
|
|||
|
|
unlink($name_file_main_setting);
|
|||
|
|
}
|
|||
|
|
$setting['columns'] = $columns;
|
|||
|
|
$setting['columnsCastom'] = $columnsCastom;
|
|||
|
|
$setting['columnsFunnels'] = $columnsFunnels;
|
|||
|
|
$setting['pages'] = $allPages;
|
|||
|
|
$setting['nameRaport'] = $nameRaport;
|
|||
|
|
$setting['arrayHeder'] = $arrayHeder;
|
|||
|
|
$setting['arrayHeder2'] = $arrayHeder2;
|
|||
|
|
$setting['rowsColumnsName'] = $rowsColumnsName;
|
|||
|
|
$setting['raport_id'] = $raport_id;
|
|||
|
|
$json_setting = json_encode($setting, JSON_UNESCAPED_UNICODE);
|
|||
|
|
file_put_contents($name_file_main_setting, $json_setting);
|
|||
|
|
}
|
|||
|
|
} else {
|
|||
|
|
$json_str = file_get_contents($_SERVER['DOCUMENT_ROOT'].'/'.$name_file_json);
|
|||
|
|
//var_dump($json_str);
|
|||
|
|
$json = json_decode($json_str, true);
|
|||
|
|
if(empty($json)){
|
|||
|
|
die("Empty json!");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$temp_reqs_ids = array();
|
|||
|
|
$allPages = (int)$json['pages'];
|
|||
|
|
$rows = (int)$json['rows'];
|
|||
|
|
if($page == $allPages){
|
|||
|
|
$cont = false;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
foreach($json['reqs_ids'] as $key => $id_req){
|
|||
|
|
if($key >= ($page-1)*$limit && $key < (($page-1)*$limit+$limit)){
|
|||
|
|
$temp_reqs_ids[] = $id_req;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
if(empty($temp_reqs_ids)){
|
|||
|
|
die("Empty reqs!");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$sql_res = "SELECT * FROM requisitions as req WHERE id in (".implode(',', $temp_reqs_ids).")";
|
|||
|
|
if(isset($_SESSION['where_req_campaigns_order'])){
|
|||
|
|
$sql_res .= $_SESSION['where_req_campaigns_order'];
|
|||
|
|
}
|
|||
|
|
// echo $sql_res;
|
|||
|
|
$q_res = $pdo->query($sql_res);
|
|||
|
|
|
|||
|
|
while($r = $pdo->fetch_assoc($q_res)){
|
|||
|
|
|
|||
|
|
$reqs[] = $r;
|
|||
|
|
$clientsIds[] = (int)$r['client_id'];
|
|||
|
|
$reqIds[] = (int)$r['id'];
|
|||
|
|
if((int)$r['object_id'] > 0){
|
|||
|
|
$objectIds[] = (int)$r['object_id'];
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
$rez['sql'] = $sql_res;
|
|||
|
|
//$cont = false;
|
|||
|
|
/* $rez['file'] = $name_file;
|
|||
|
|
$rez['result'] = 'ok';
|
|||
|
|
$rez['name'] = $name;
|
|||
|
|
$rez['cont'] = $cont;
|
|||
|
|
$rez['json'] = $json;
|
|||
|
|
$rez['page'] = $page;
|
|||
|
|
$rez['sql'] = $sql_res;
|
|||
|
|
echo json_encode($rez);
|
|||
|
|
exit();*/
|
|||
|
|
}
|
|||
|
|
/*$limitSql = ' limit '.($page-1)*$limit.', '.$limit;
|
|||
|
|
if(isset($_SESSION['where_req'])){
|
|||
|
|
$sql_res = $_SESSION['where_req'].$limitSql;
|
|||
|
|
} else {
|
|||
|
|
if($raport_id > 0){
|
|||
|
|
$sql_res = $sql." order by funnel_id, created_at desc ".$limitSql;
|
|||
|
|
} else {
|
|||
|
|
$sql_res = $sql." order by created_at desc ".$limitSql;
|
|||
|
|
}
|
|||
|
|
}*/
|
|||
|
|
//$rez['sql']=$sql_res;
|
|||
|
|
/*if($raport_id > 0){
|
|||
|
|
$sql_res = $sql." order by funnel_id, created_at desc ".$limitSql;
|
|||
|
|
} else {
|
|||
|
|
$sql_res = $sql." order by created_at desc ".$limitSql;
|
|||
|
|
}*/
|
|||
|
|
//$rez['sql'] = $sql_res;
|
|||
|
|
/*$q_res = $pdo->query($sql_res);
|
|||
|
|
while($r = $pdo->fetch_assoc($q_res)){
|
|||
|
|
$reqs[] = $r;
|
|||
|
|
$clientsIds[] = (int)$r['client_id'];
|
|||
|
|
$reqIds[] = (int)$r['id'];
|
|||
|
|
if((int)$r['object_id'] > 0){
|
|||
|
|
$objectIds[] = (int)$r['object_id'];
|
|||
|
|
}
|
|||
|
|
}*/
|
|||
|
|
|
|||
|
|
if(empty($reqs)){
|
|||
|
|
die("Empty Reqs");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//Список клиентов
|
|||
|
|
if(!empty($clientsIds)){
|
|||
|
|
$sql_clients = "SELECT * FROM clients WHERE id in (".implode(',',$clientsIds).")";
|
|||
|
|
$q_clients = $pdo->query($sql_clients);
|
|||
|
|
while($r_clients = $pdo->fetch_assoc($q_clients)){
|
|||
|
|
$clients[$r_clients['id']] = $r_clients;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//Типы заявок
|
|||
|
|
if($type == 'req'){
|
|||
|
|
$reqClass = new Requisitions($pdo, true);
|
|||
|
|
$reqClass->set_agencyId($agency_id);
|
|||
|
|
$typesReq = $reqClass->get_type();
|
|||
|
|
$reqTypes = $typesReq['names_type'];
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
//Список источников
|
|||
|
|
$usersIds = array_keys($users);
|
|||
|
|
$sql_source = "SELECT * FROM `advertising_sources` WHERE user_id in (".implode(',',$usersIds).")";
|
|||
|
|
|
|||
|
|
$q_source = $pdo->query($sql_source);
|
|||
|
|
while($r_source = $pdo->fetch_assoc($q_source)){
|
|||
|
|
$sources[$r_source['id']] = $r_source;
|
|||
|
|
}
|
|||
|
|
$sources[10000] = array('source'=>'Объект нашей компании');
|
|||
|
|
|
|||
|
|
//Список партнеров
|
|||
|
|
$sql_emp = "SELECT * FROM `partners_employees` WHERE created_by in (".implode(',', $usersIds).")";
|
|||
|
|
$q_emp = $pdo->query($sql_emp);
|
|||
|
|
while($r_emp = $pdo->fetch_assoc($q_emp)){
|
|||
|
|
$partners[$r_emp['id']] = $r_emp;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//Причины закрытия
|
|||
|
|
$sql_d = "SELECT * FROM denial_work WHERE (agency_id = {$agency_id} or agency_id = 0)";
|
|||
|
|
if($agency_id == 19895){
|
|||
|
|
$sql_d = "SELECT * FROM denial_work WHERE agency_id = {$agency_id} AND del = 0";
|
|||
|
|
}
|
|||
|
|
$q_d = $pdo->query($sql_d);
|
|||
|
|
while($r_d = $pdo->fetch_assoc($q_d)){
|
|||
|
|
$denials[$r_d['id']] = $r_d;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
$Fields = array();
|
|||
|
|
$fields_req = $reqClass->get_req_fields($agency_id);
|
|||
|
|
if(isset($fields_req['fields'])){
|
|||
|
|
foreach($fields_req['fields'] as $fr){
|
|||
|
|
$Fields[$fr['id']] = $fr;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$req_step_values = array();
|
|||
|
|
if(!empty($reqIds)){
|
|||
|
|
//Заполненные этапы
|
|||
|
|
$sql_fs = "SELECT * FROM `clients_funnel_step` WHERE `req_id` in (".implode(',', $reqIds).")";
|
|||
|
|
|
|||
|
|
// try{
|
|||
|
|
$q_fs= $pdo->query($sql_fs);
|
|||
|
|
|
|||
|
|
while($r_fs = $pdo->fetch_assoc($q_fs)){
|
|||
|
|
if(!empty($r_fs['values'])){
|
|||
|
|
$tempValues = array();
|
|||
|
|
// echo str_replace("\n", " ", $r_fs['values']);
|
|||
|
|
$str_value = htmlspecialchars_decode($r_fs['values']);
|
|||
|
|
$str_value = str_replace("\\n", " ", $str_value);
|
|||
|
|
$str_value = str_replace("\n", " ", $str_value);
|
|||
|
|
$str_value = str_replace("\\\"", "", $str_value);
|
|||
|
|
$str_value = str_replace("\\", "", $str_value);
|
|||
|
|
|
|||
|
|
// $str_value = stripcslashes($str_value);
|
|||
|
|
$str_value = str_replace(['"{', '}"'], ['{', '}'], $str_value);
|
|||
|
|
|
|||
|
|
|
|||
|
|
// $valarr = json_decode($str_value,true);
|
|||
|
|
|
|||
|
|
// $str_value = str_replace(['"{', '}"'], ['{', '}'], $str_value);
|
|||
|
|
$valarr = json_decode($str_value,true);
|
|||
|
|
if(empty($valarr)){
|
|||
|
|
$temp_str_value = str_replace("\\", "-", $str_value);
|
|||
|
|
$valarr = json_decode($temp_str_value,true);
|
|||
|
|
}
|
|||
|
|
if(empty($valarr)){
|
|||
|
|
// if(empty($valarr)){
|
|||
|
|
$temp_str_value = str_replace("\\", "/", $str_value);;
|
|||
|
|
$valarr = json_decode($temp_str_value,true);
|
|||
|
|
// }
|
|||
|
|
}
|
|||
|
|
if(empty($valarr)){
|
|||
|
|
// if(empty($valarr)){
|
|||
|
|
$temp_str_value = str_replace("\\", "", $str_value);;
|
|||
|
|
$valarr = json_decode($temp_str_value,true);
|
|||
|
|
// }
|
|||
|
|
}
|
|||
|
|
/* if($r_fs['req_id'] == 662714){
|
|||
|
|
echo $str_value."\n";
|
|||
|
|
var_dump($valarr);
|
|||
|
|
}*/
|
|||
|
|
// var_dump($valarr);
|
|||
|
|
$arr = $valarr;
|
|||
|
|
foreach($arr as $key_av => $arrVal){
|
|||
|
|
/* if($r_fs['req_id'] == 662714){
|
|||
|
|
echo $arrVal."\n";
|
|||
|
|
var_dump(json_decode($arrVal, true));
|
|||
|
|
}*/
|
|||
|
|
$tempValues[$key_av] = $arrVal;
|
|||
|
|
}
|
|||
|
|
$req_step_values[$r_fs['req_id']] = $tempValues;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
//var_dump($req_step_values);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
//Кастомные поля
|
|||
|
|
//if($raport_id > 0){
|
|||
|
|
$sql_castom = "SELECT * FROM `requisition_data` where `req_id` in (".implode(',', $reqIds).")";
|
|||
|
|
$q_castom = $pdo->query($sql_castom);
|
|||
|
|
while($r_castom = $pdo->fetch_assoc($q_castom)) {
|
|||
|
|
$castom_data[$r_castom['req_id']][$r_castom['field_id']] = $r_castom;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//Объекты
|
|||
|
|
if(!empty($objectIds) && ($columns['nazv_obj']== 1 || $columns['address_obj']== 1 || $columns['address_obj']== 1 || $columns['price_obj']== 1 )){
|
|||
|
|
$sql_objects = "SELECT id, adres, nazv, stoim FROM objects WHERE id in (".implode(',', $objectIds).")";
|
|||
|
|
$r_objects = $pdo->query($sql_objects);
|
|||
|
|
while($obj = $pdo->fetch_assoc($r_objects)){
|
|||
|
|
$objects[$obj['id']] = $obj;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
//if($raport_id > 0){
|
|||
|
|
$eventsReqsDelete = array();
|
|||
|
|
$eventsReqs = array();
|
|||
|
|
//Этапы
|
|||
|
|
$eventsReqsDBO = $pdo->query("SELECT * FROM events_clients where (event='stageedit' or event='delete') AND req_id in (".implode(',', $reqIds).")");
|
|||
|
|
|
|||
|
|
while ($row_e = $pdo->fetch_assoc($eventsReqsDBO)) {
|
|||
|
|
if($row_e['event'] == 'stageedit'){
|
|||
|
|
if(isset($users[$row_e['user_id']])){
|
|||
|
|
$user = $users[$row_e['user_id']];
|
|||
|
|
$row_e['user'] = trim($user['last_name'] . ' ' . $user['first_name'] . ' ' . $user['middle_name']);
|
|||
|
|
} else {
|
|||
|
|
$row_e['user'] = '';
|
|||
|
|
}
|
|||
|
|
$eventsReqs[$row_e['req_id']][] = $row_e;
|
|||
|
|
}
|
|||
|
|
if($row_e['event'] == 'delete'){
|
|||
|
|
$eventsReqsDelete[$row_e['req_id']] = $row_e['date_update'];
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
// var_dump($eventsReqs);
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
//Договоры
|
|||
|
|
$sql_contracts = "SELECT *, (SELECT type FROM contract_types WHERE id=contracts_req.type) as type_name FROM `contracts_req` WHERE req_id in (".implode(',', $reqIds).")";
|
|||
|
|
$q_contract = $pdo->query($sql_contracts);
|
|||
|
|
while($contract = $pdo->fetch_assoc($q_contract)){
|
|||
|
|
if(!isset($contracts[$contract['req_id']])){
|
|||
|
|
$contracts[$contract['req_id']] = Array('types'=>'', 'numbers'=>'', 'dates'=>'');
|
|||
|
|
}
|
|||
|
|
if($contracts[$contract['req_id']]['types'] != '') $contracts[$contract['req_id']]['types'] .= '; ';
|
|||
|
|
if($contracts[$contract['req_id']]['numbers'] != '') $contracts[$contract['req_id']]['numbers'] .= '; ';
|
|||
|
|
if($contracts[$contract['req_id']]['dates'] != '') $contracts[$contract['req_id']]['dates'] .= '; ';
|
|||
|
|
$contracts[$contract['req_id']]['types'] .= $contract['type_name'];
|
|||
|
|
$contracts[$contract['req_id']]['numbers'] .= '№'.$contract['number'];
|
|||
|
|
$contracts[$contract['req_id']]['dates'] .= date('d.m.Y',$contract['date_start']).' - '.date('d.m.Y',$contract['date_end']);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//Теги
|
|||
|
|
$sql_a = "SELECT *, (SELECT name FROM activities WHERE activities.id = requisitions_activities.activity_id) as name FROM requisitions_activities WHERE req_id in (".implode(',', $reqIds).")";
|
|||
|
|
|
|||
|
|
$arr_name_activities = [];
|
|||
|
|
$q_a = $pdo->query($sql_a, true);
|
|||
|
|
while($r_a = $pdo->fetch_assoc($q_a)){
|
|||
|
|
if(!isset($arr_name_activities[$r_a['req_id']])) $arr_name_activities[$r_a['req_id']] = [];
|
|||
|
|
$arr_name_activities[$r_a['req_id']][] = $r_a['name'];
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//Задачи
|
|||
|
|
$events = array();
|
|||
|
|
$eventsText = array();
|
|||
|
|
$eventsCallText = array();
|
|||
|
|
|
|||
|
|
$sql_event = "SELECT * FROM user_client_events WHERE req_id>0 and req_id in (".implode(',', $reqIds).") and cancel=0 AND type != 'step' AND type != 'file' AND type !='show_object' order by id desc";
|
|||
|
|
$q_event = $pdo->query($sql_event);
|
|||
|
|
while($r_event = $pdo->fetch_assoc($q_event)){
|
|||
|
|
$events[$r_event['req_id']][$r_event['id']] = $r_event;
|
|||
|
|
}
|
|||
|
|
$arrNameEvent = ['call'=>'Звонок', 'even'=>'Мероприятие', 'deal'=>'Cделка', 'show'=>'Показ', 'meet'=>'Встреча', 'comment'=>'Примечание'];
|
|||
|
|
foreach($events as $idr => $event){
|
|||
|
|
$e_i = 0;
|
|||
|
|
$temp_text = '';
|
|||
|
|
$temp_text_call = '';
|
|||
|
|
foreach($event as $ide => $ev){
|
|||
|
|
if($e_i < 3) {
|
|||
|
|
if($ev['type'] == 'call' && ($ev['mango'] == 1 || $ev['megafon'] == 1 || $ev['telphin'] == 1 || $ev['beeline'] == 1 || $ev['mts'] == 1 || $ev['tele2'] == 1 || $ev['allo'] == 1)){
|
|||
|
|
|
|||
|
|
if($temp_text_call != '') $temp_text_call .= "\n\t\n\t";
|
|||
|
|
|
|||
|
|
$temp_text_call .= $arrNameEvent[$ev['type']];
|
|||
|
|
if(!empty($ev['name'])){
|
|||
|
|
$temp_text_call .= " Наименование ".$ev['name'];
|
|||
|
|
}
|
|||
|
|
if(!empty($ev['comment'])){
|
|||
|
|
$temp_text_call .= " Комментарий: ".$ev['comment'];
|
|||
|
|
}
|
|||
|
|
if(!empty($ev['address'])){
|
|||
|
|
$temp_text_call .= " Адрес: ".$ev['address'];
|
|||
|
|
}
|
|||
|
|
if(!empty($ev['summa'])){
|
|||
|
|
$temp_text_call .= " Сумма: ".$ev['summa'];
|
|||
|
|
}
|
|||
|
|
$temp_text_call .= " Время создания: ".date('d.m.Y H:i', strtotime($ev['create_date']));
|
|||
|
|
if(!empty($ev['schedule_date'])){
|
|||
|
|
$temp_text_call .= " Cрок: ".date('d.m.Y H:i', strtotime($ev['schedule_date']));
|
|||
|
|
}
|
|||
|
|
} else {
|
|||
|
|
if($temp_text != '') $temp_text .= "\n\t\n\t";
|
|||
|
|
|
|||
|
|
$temp_text .= $arrNameEvent[$ev['type']];
|
|||
|
|
if(!empty($ev['name'])){
|
|||
|
|
$temp_text .= " Наименование ".$ev['name'];
|
|||
|
|
}
|
|||
|
|
if(!empty($ev['comment'])){
|
|||
|
|
$temp_text .= " Комментарий: ".$ev['comment'];
|
|||
|
|
}
|
|||
|
|
if(!empty($ev['address'])){
|
|||
|
|
$temp_text .= " Адрес: ".$ev['address'];
|
|||
|
|
}
|
|||
|
|
if(!empty($ev['summa'])){
|
|||
|
|
$temp_text .= " Сумма: ".$ev['summa'];
|
|||
|
|
}
|
|||
|
|
$temp_text .= " Время создания: ".date('d.m.Y H:i', strtotime($ev['create_date']));
|
|||
|
|
if(!empty($ev['schedule_date'])){
|
|||
|
|
$temp_text .= " Cрок: ".date('d.m.Y H:i', strtotime($ev['schedule_date']));
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
$eventsText[$idr] = $temp_text;
|
|||
|
|
$eventsCallText[$idr] = $temp_text_call;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
$row = (($page-1)*$limit) + 2;
|
|||
|
|
if($raport_id > 0){
|
|||
|
|
$row++;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
//var_dump(($filter->stages->work == 0 && $raport_id == 0));
|
|||
|
|
//var_dump($columns);
|
|||
|
|
|
|||
|
|
if(file_exists($name_file_main_json."_".$page.".json")){
|
|||
|
|
// $json_str = file_get_contents($name_file_main_json);
|
|||
|
|
unlink($name_file_main_json."_".$page.".json");
|
|||
|
|
//var_dump($json_str);
|
|||
|
|
/* $jsons = json_decode($json_str, true);
|
|||
|
|
//var_dump($jsons);
|
|||
|
|
foreach($jsons as $json){
|
|||
|
|
$result[] = $json;
|
|||
|
|
}*/
|
|||
|
|
}
|
|||
|
|
foreach($reqs as $res){
|
|||
|
|
$rez = array();
|
|||
|
|
if(isset($columns['id']) && $columns['id'] == 1){
|
|||
|
|
$rez['id'] = $res['id'];
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
if($columns['date'] == 1){
|
|||
|
|
$rez['date'] = date('d.m.Y', strtotime($res['created_at']));
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
if($columns['responsible'] == 1){
|
|||
|
|
|
|||
|
|
$responsible = '';
|
|||
|
|
if($res['who_work'] > 0 && isset($users[$res['who_work']])){
|
|||
|
|
$responsible = trim($users[$res['who_work']]['last_name'].' '.$users[$res['who_work']]['first_name'].' '.$users[$res['who_work']]['middle_name']);
|
|||
|
|
}
|
|||
|
|
$rez['responsible'] = $responsible;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
if($columns['client'] == 1){
|
|||
|
|
|
|||
|
|
$client = '';
|
|||
|
|
if(isset($clients[$res['client_id']])){
|
|||
|
|
$client = $clients[$res['client_id']]['fio'];
|
|||
|
|
}
|
|||
|
|
$rez['client'] = $client;
|
|||
|
|
}
|
|||
|
|
if($columns['phone'] == 1){
|
|||
|
|
|
|||
|
|
$phone = '';
|
|||
|
|
if(isset($clients[$res['client_id']])){
|
|||
|
|
$phone = $clients[$res['client_id']]['phone'];
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$rez['phone'] = $phone;
|
|||
|
|
}
|
|||
|
|
if($columns['email'] == 1){
|
|||
|
|
|
|||
|
|
$email = '';
|
|||
|
|
if(isset($clients[$res['client_id']])){
|
|||
|
|
$email = $clients[$res['client_id']]['email'];
|
|||
|
|
}
|
|||
|
|
$rez['email'] = $email;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
if($columns['type_req'] == 1){
|
|||
|
|
|
|||
|
|
$typeName = '';
|
|||
|
|
if(isset($reqTypes[$res['type_id']])){
|
|||
|
|
$typeName = $reqTypes[$res['type_id']]['name'];
|
|||
|
|
}
|
|||
|
|
$rez['type_req'] = $typeName;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if($columns['contract_type']){
|
|||
|
|
$contract_types = '';
|
|||
|
|
if(isset($contracts[$res['id']]['types'])){
|
|||
|
|
$contract_types = $contracts[$res['id']]['types'];
|
|||
|
|
}
|
|||
|
|
$rez['contract_type'] = $contract_types;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if($columns['contract_number']){
|
|||
|
|
$contract_number = '';
|
|||
|
|
if(isset($contracts[$res['id']]['numbers'])){
|
|||
|
|
$contract_number = $contracts[$res['id']]['numbers'];
|
|||
|
|
}
|
|||
|
|
$rez['contract_number'] = $contract_number;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if($columns['contract_date']){
|
|||
|
|
$contract_date = '';
|
|||
|
|
if(isset($contracts[$res['id']]['dates'])){
|
|||
|
|
$contract_date = $contracts[$res['id']]['dates'];
|
|||
|
|
}
|
|||
|
|
$rez['contract_date'] = $contract_date;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if($columns['nazv_obj'] == 1){
|
|||
|
|
$nazv_obj = '';
|
|||
|
|
if(isset($objects[$res['object_id']])){
|
|||
|
|
$nazv_obj = $objects[$res['object_id']]['nazv'];
|
|||
|
|
}
|
|||
|
|
$rez['nazv_obj'] = $nazv_obj;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if($columns['address_obj'] == 1){
|
|||
|
|
$address_obj = '';
|
|||
|
|
if(isset($objects[$res['object_id']])){
|
|||
|
|
$address_obj = $objects[$res['object_id']]['adres'];
|
|||
|
|
}
|
|||
|
|
$rez['address_obj'] = $address_obj;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if($columns['price_obj'] == 1){
|
|||
|
|
$price_obj = '';
|
|||
|
|
if(isset($objects[$res['object_id']])){
|
|||
|
|
$price_obj = $objects[$res['object_id']]['stoim'];
|
|||
|
|
}
|
|||
|
|
$rez['price_obj'] = $price_obj;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if($columns['funnel'] == 1){
|
|||
|
|
|
|||
|
|
$funnelName = '';
|
|||
|
|
if($res['funnel_id'] == 0){
|
|||
|
|
$funnelName = 'Звонки';
|
|||
|
|
}
|
|||
|
|
if(isset($funnels[$res['funnel_id']])){
|
|||
|
|
$funnelName = $funnels[$res['funnel_id']]['name'];
|
|||
|
|
}
|
|||
|
|
$rez['funnel'] = $funnelName;
|
|||
|
|
}
|
|||
|
|
if($columns['tag'] == 1){
|
|||
|
|
$tagName = '';
|
|||
|
|
if(isset($arr_name_activities[$res['id']]) && !empty($arr_name_activities[$res['id']])){
|
|||
|
|
$tagName = implode(', ', $arr_name_activities[$res['id']]);
|
|||
|
|
}
|
|||
|
|
$rez['tag'] = $tagName;
|
|||
|
|
}
|
|||
|
|
if($columns['step'] == 1){
|
|||
|
|
|
|||
|
|
$stepName = '';
|
|||
|
|
if($res['deleted'] == 1){
|
|||
|
|
$stepName = 'Закрыта';
|
|||
|
|
} else
|
|||
|
|
if($res['funnel_id'] == 0){
|
|||
|
|
$stepName = $arrEtap[$res['stage']];
|
|||
|
|
} else
|
|||
|
|
if(isset($steps[$res['step_id']])){
|
|||
|
|
$stepName = $steps[$res['step_id']]['name'];
|
|||
|
|
}
|
|||
|
|
$rez['step'] = $stepName;
|
|||
|
|
}
|
|||
|
|
if($columns['name_req'] == 1){
|
|||
|
|
|
|||
|
|
|
|||
|
|
$rez['name_req'] = $res['name'];
|
|||
|
|
}
|
|||
|
|
if($columns['desc'] == 1){
|
|||
|
|
|
|||
|
|
$rez['desc'] = html_entity_decode($res['description']);
|
|||
|
|
}
|
|||
|
|
if($columns['events'] == 1){
|
|||
|
|
$text_event = '';
|
|||
|
|
if(isset($eventsText[$res['id']])){
|
|||
|
|
$text_event = htmlspecialchars_decode($eventsText[$res['id']]);
|
|||
|
|
}
|
|||
|
|
$rez['events'] = $text_event;
|
|||
|
|
}
|
|||
|
|
if($columns['events_call'] == 1){
|
|||
|
|
$text_event_text = '';
|
|||
|
|
if(isset($eventsCallText[$res['id']])){
|
|||
|
|
$text_event_text = htmlspecialchars_decode($eventsCallText[$res['id']]);
|
|||
|
|
}
|
|||
|
|
$rez['events_call'] = $text_event_text;
|
|||
|
|
}
|
|||
|
|
if($columns['source'] == 1){
|
|||
|
|
|
|||
|
|
$sourceName = '';
|
|||
|
|
if(isset($sources[$res['source']])){
|
|||
|
|
$sourceName = $sources[$res['source']]['source'];
|
|||
|
|
}
|
|||
|
|
$rez['source'] = $sourceName;
|
|||
|
|
}
|
|||
|
|
if($columns['partner'] == 1){
|
|||
|
|
|
|||
|
|
$partnerName = '';
|
|||
|
|
if(isset($partners[$res['employee_id']])){
|
|||
|
|
$partnerName = $partners[$res['employee_id']]['name'];
|
|||
|
|
}
|
|||
|
|
$rez['partner'] = $partnerName;
|
|||
|
|
}
|
|||
|
|
if($columns['denial'] == 1){
|
|||
|
|
|
|||
|
|
$denialName = 'Другое';
|
|||
|
|
if($res['deleted'] == 1){
|
|||
|
|
if(isset($denials[$res['denial']])){
|
|||
|
|
$denialName = $denials[$res['denial']]['name'];
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
if($res['confirm'] == 10){
|
|||
|
|
$denialName = "Совершена сделка на ".$res['summa']." руб.";
|
|||
|
|
}
|
|||
|
|
$rez['denial'] = $denialName;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
if($columns['denial_comm'] == 1){
|
|||
|
|
|
|||
|
|
$reason = $res['reason'];
|
|||
|
|
if(isset($denialName)){
|
|||
|
|
$reason = str_replace($denialName.'.','',$reason);
|
|||
|
|
}
|
|||
|
|
$rez['denial_comm'] = $reason;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
if($columns['summa'] == 1){
|
|||
|
|
|
|||
|
|
|
|||
|
|
$rez['summa'] = $res['summa'];
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
if($columns['date_close'] == 1){
|
|||
|
|
$rez['date_close'] = '';
|
|||
|
|
if(isset($eventsReqsDelete[$res['id']])){
|
|||
|
|
$rez['date_close'] = date('d.m.Y H:i', strtotime($eventsReqsDelete[$res['id']]));
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
foreach($columnsCastom as $key => $column){
|
|||
|
|
if($column['val'] == 1){
|
|||
|
|
$fieldId = str_replace('column','',$key);
|
|||
|
|
$value = '';
|
|||
|
|
// var_dump($castom_data[$res['id']][$fieldId]);
|
|||
|
|
if(isset($castom_data[$res['id']][$fieldId])){
|
|||
|
|
if($Fields[$fieldId]['is_podgroup'] > 0){
|
|||
|
|
if($Fields[$fieldId]['is_podgroup_multiselect'] == 1){
|
|||
|
|
foreach($Fields[$fieldId]['podgroups'] as $pd){
|
|||
|
|
$values = json_decode($castom_data[$res['id']][$fieldId]['value'],true);
|
|||
|
|
if(in_array($pd['id'], $values)){
|
|||
|
|
if(isset($pd['title'])){
|
|||
|
|
if($value != '') $value .= "\n";
|
|||
|
|
$value .= $pd['title'];
|
|||
|
|
if(isset($castom_data[$res['id']][$pd['id']]['value'])){
|
|||
|
|
$valTemp = $castom_data[$res['id']][$pd['id']]['value'];
|
|||
|
|
if(isset($pd['params']['items'])){
|
|||
|
|
foreach($pd['params']['items'] as $item){
|
|||
|
|
if($item['value'] == $castom_data[$res['id']][$pd['id']]['value']){
|
|||
|
|
$valTemp = $item['name'];
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
//$val = $pd['params']['items'][$arrFields_models['modelp_'.$value]]['name'];
|
|||
|
|
}
|
|||
|
|
$value .= ": ".$valTemp;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
} else {
|
|||
|
|
|
|||
|
|
foreach($Fields[$fieldId]['podgroups'] as $pd){
|
|||
|
|
if($pd['id'] == $castom_data[$res['id']][$fieldId]['value']){
|
|||
|
|
if(isset($pd['title'])){
|
|||
|
|
$value .= $pd['title'];
|
|||
|
|
if(isset($castom_data[$res['id']][$pd['id']]['value'])){
|
|||
|
|
$valTemp = $castom_data[$res['id']][$pd['id']]['value'];
|
|||
|
|
if(isset($pd['params']['items'])){
|
|||
|
|
foreach($pd['params']['items'] as $item){
|
|||
|
|
if($item['value'] == $castom_data[$res['id']][$pd['id']]['value']){
|
|||
|
|
$valTemp = $item['name'];
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
//$val = $pd['params']['items'][$arrFields_models['modelp_'.$value]]['name'];
|
|||
|
|
}
|
|||
|
|
$value .= ": ".$valTemp;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
} else if($Fields[$fieldId]['type'] == 2){
|
|||
|
|
// var_dump($Fields[$fieldId]);
|
|||
|
|
if(isset($Fields[$fieldId]['params']['items'])){
|
|||
|
|
foreach($Fields[$fieldId]['params']['items'] as $item){
|
|||
|
|
if($item['value'] == $castom_data[$res['id']][$fieldId]['value']){
|
|||
|
|
$value = $item['name'];
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
} else if($Fields[$fieldId]['type'] == 3){
|
|||
|
|
// var_dump($Fields[$fieldId]);
|
|||
|
|
$value = '';
|
|||
|
|
foreach(json_decode($castom_data[$res['id']][$fieldId]['value']) as $val){
|
|||
|
|
if(isset($Fields[$fieldId]['params']['items'])){
|
|||
|
|
foreach($Fields[$fieldId]['params']['items'] as $item){
|
|||
|
|
if($item['value'] == $val){
|
|||
|
|
if($value != '') $value .= ", ";
|
|||
|
|
$value .= $item['name'];
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
} else {
|
|||
|
|
$value = trim($castom_data[$res['id']][$fieldId]['value'], "\"");
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
// echo $key.' '.$litRow."\n";
|
|||
|
|
$rez[$key] = $value;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (isset($columnsFunnels)){
|
|||
|
|
foreach ($columnsFunnels as $key_cf => $cf){
|
|||
|
|
// var_dump($cf);
|
|||
|
|
if($cf['is_active'] == 1 && isset($funnels[$key_cf])){
|
|||
|
|
|
|||
|
|
foreach($cf['steps'] as $key_st => $st){
|
|||
|
|
$nameStep = $steps[str_replace('step','',$key_st)]['name'];
|
|||
|
|
if(isset($eventsReqs[$res['id']])){
|
|||
|
|
foreach($eventsReqs[$res['id']] as $event){
|
|||
|
|
$qwe = explode('«', $event['dop_text'])[1];
|
|||
|
|
$qwe2 = explode('»',$qwe)[0];
|
|||
|
|
if(trim($qwe2) == trim($nameStep)){
|
|||
|
|
if($st['is_active'] == 1){
|
|||
|
|
$rez[$key_st] = '';
|
|||
|
|
$rez[str_replace('step','step_2',$key_st)] = '';
|
|||
|
|
// $litRow = $rowsColumnsName['step'.$key_st];
|
|||
|
|
$date = '';
|
|||
|
|
if(isset($event['date']) && !empty($event['date'])){
|
|||
|
|
$date = date('d.m.Y', strtotime($event['date']));
|
|||
|
|
}
|
|||
|
|
$rez[$key_st] = $date;
|
|||
|
|
|
|||
|
|
|
|||
|
|
$rez[str_replace('step','step_2',$key_st)] = $event['user'];
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
foreach($st['fields'] as $key_stf => $stf){
|
|||
|
|
if($stf['is_active'] == 1){
|
|||
|
|
$val_stf = '';
|
|||
|
|
if(isset($req_step_values[$res['id']][str_replace('step','',$key_st)][$key_stf]) && !is_array($req_step_values[$res['id']][str_replace('step','',$key_st)][$key_stf]['value'])){
|
|||
|
|
$val_stf = $req_step_values[$res['id']][str_replace('step','',$key_st)][$key_stf]['value'];
|
|||
|
|
if(($req_step_values[$res['id']][str_replace('step','',$key_st)][$key_stf]['type'] == 'date' || $req_step_values[$res['id']][str_replace('step','',$key_st)][$key_stf]['type'] == 'datetime') && !empty($req_step_values[$res['id']][str_replace('step','',$key_st)][$key_stf]['value'])){
|
|||
|
|
$val_stf = date('d.m.Y', strtotime($req_step_values[$res['id']][str_replace('step','',$key_st)][$key_stf]['value']));
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
$rez[$key_st.'field'.$key_stf] = $val_stf;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$result[] = $rez;
|
|||
|
|
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
$json_main = json_encode($result, JSON_UNESCAPED_UNICODE);
|
|||
|
|
file_put_contents($name_file_main_json."_".$page.".json", $json_main, FILE_APPEND);
|
|||
|
|
|
|||
|
|
/*foreach($reqs as $res){
|
|||
|
|
if(isset($columns['id']) && $columns['id'] == 1){
|
|||
|
|
$litRow = $rowsColumnsName['id'];
|
|||
|
|
$sheet->setCellValue($litRow.$row, $res['id']);
|
|||
|
|
}
|
|||
|
|
if($columns['date'] == 1){
|
|||
|
|
$litRow = $rowsColumnsName['date'];
|
|||
|
|
$sheet->setCellValue($litRow.$row, date('d.m.Y', strtotime($res['created_at'])));
|
|||
|
|
}
|
|||
|
|
if($columns['responsible'] == 1){
|
|||
|
|
$litRow = $rowsColumnsName['responsible'];
|
|||
|
|
$responsible = '';
|
|||
|
|
if($res['who_work'] > 0 && isset($users[$res['who_work']])){
|
|||
|
|
$responsible = trim($users[$res['who_work']]['last_name'].' '.$users[$res['who_work']]['first_name'].' '.$users[$res['who_work']]['middle_name']);
|
|||
|
|
}
|
|||
|
|
$sheet->setCellValue($litRow.$row, $responsible);
|
|||
|
|
}
|
|||
|
|
if($columns['client'] == 1){
|
|||
|
|
$litRow = $rowsColumnsName['client'];
|
|||
|
|
$client = '';
|
|||
|
|
if(isset($clients[$res['client_id']])){
|
|||
|
|
$client = $hideContactsExcel
|
|||
|
|
? mask_fio_if_contains_phone($clients[$res['client_id']]['fio'])
|
|||
|
|
: $clients[$res['client_id']]['fio'];
|
|||
|
|
}
|
|||
|
|
$sheet->setCellValue($litRow.$row, $client);
|
|||
|
|
}
|
|||
|
|
if($columns['phone'] == 1){
|
|||
|
|
$litRow = $rowsColumnsName['phone'];
|
|||
|
|
$phone = '';
|
|||
|
|
if(isset($clients[$res['client_id']])){
|
|||
|
|
$phone = $hideContactsExcel
|
|||
|
|
? mask_client_phone($clients[$res['client_id']]['phone'])
|
|||
|
|
: $clients[$res['client_id']]['phone'];
|
|||
|
|
}
|
|||
|
|
$sheet->setCellValue($litRow.$row, $phone);
|
|||
|
|
}
|
|||
|
|
if($columns['email'] == 1){
|
|||
|
|
$litRow = $rowsColumnsName['email'];
|
|||
|
|
$email = '';
|
|||
|
|
if(isset($clients[$res['client_id']])){
|
|||
|
|
$email = $hideContactsExcel
|
|||
|
|
? mask_client_email($clients[$res['client_id']]['email'])
|
|||
|
|
: $clients[$res['client_id']]['email'];
|
|||
|
|
}
|
|||
|
|
$sheet->setCellValue($litRow.$row, $email);
|
|||
|
|
}
|
|||
|
|
if($columns['type_req'] == 1){
|
|||
|
|
$litRow = $rowsColumnsName['type_req'];
|
|||
|
|
$typeName = '';
|
|||
|
|
if(isset($reqTypes[$res['type_id']])){
|
|||
|
|
$typeName = $reqTypes[$res['type_id']]['name'];
|
|||
|
|
}
|
|||
|
|
$sheet->setCellValue($litRow.$row, $typeName);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if($columns['nazv_obj'] == 1){
|
|||
|
|
$nazv_obj = '';
|
|||
|
|
$litRow = $rowsColumnsName['nazv_obj'];
|
|||
|
|
if(isset($objects[$res['object_id']])){
|
|||
|
|
$nazv_obj = $objects[$res['object_id']]['nazv'];
|
|||
|
|
}
|
|||
|
|
$sheet->setCellValue($litRow.$row, $nazv_obj);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if($columns['address_obj'] == 1){
|
|||
|
|
$address_obj = '';
|
|||
|
|
$litRow = $rowsColumnsName['address_obj'];
|
|||
|
|
if(isset($objects[$res['object_id']])){
|
|||
|
|
$address_obj = $objects[$res['object_id']]['adres'];
|
|||
|
|
}
|
|||
|
|
$sheet->setCellValue($litRow.$row, $address_obj);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if($columns['price_obj'] == 1){
|
|||
|
|
$price_obj = '';
|
|||
|
|
$litRow = $rowsColumnsName['price_obj'];
|
|||
|
|
if(isset($objects[$res['object_id']])){
|
|||
|
|
$price_obj = $objects[$res['object_id']]['stoim'];
|
|||
|
|
}
|
|||
|
|
$sheet->setCellValue($litRow.$row, $price_obj);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if($columns['funnel'] == 1){
|
|||
|
|
$litRow = $rowsColumnsName['funnel'];
|
|||
|
|
$funnelName = '';
|
|||
|
|
if($res['funnel_id'] == 0){
|
|||
|
|
$funnelName = 'Звонки';
|
|||
|
|
}
|
|||
|
|
if(isset($funnels[$res['funnel_id']])){
|
|||
|
|
$funnelName = $funnels[$res['funnel_id']]['name'];
|
|||
|
|
}
|
|||
|
|
$sheet->setCellValue($litRow.$row, $funnelName);
|
|||
|
|
}
|
|||
|
|
if($columns['step'] == 1){
|
|||
|
|
$litRow = $rowsColumnsName['step'];
|
|||
|
|
$stepName = '';
|
|||
|
|
if($res['deleted'] == 1){
|
|||
|
|
$stepName = 'Закрыта';
|
|||
|
|
} else
|
|||
|
|
if($res['funnel_id'] == 0){
|
|||
|
|
$stepName = $arrEtap[$res['stage']];
|
|||
|
|
} else
|
|||
|
|
if(isset($steps[$res['step_id']])){
|
|||
|
|
$stepName = $steps[$res['step_id']]['name'];
|
|||
|
|
}
|
|||
|
|
$sheet->setCellValue($litRow.$row, $stepName);
|
|||
|
|
}
|
|||
|
|
if($columns['name_req'] == 1){
|
|||
|
|
$litRow = $rowsColumnsName['name_req'];
|
|||
|
|
|
|||
|
|
$sheet->setCellValue($litRow.$row, $res['name']);
|
|||
|
|
}
|
|||
|
|
if($columns['desc'] == 1){
|
|||
|
|
$litRow = $rowsColumnsName['desc'];
|
|||
|
|
$sheet->setCellValue($litRow.$row, str_replace(['<br>','</br>','<br />'],"\n",html_entity_decode($res['description'])));
|
|||
|
|
}
|
|||
|
|
if($columns['source'] == 1){
|
|||
|
|
$litRow = $rowsColumnsName['source'];
|
|||
|
|
$sourceName = '';
|
|||
|
|
if(isset($sources[$res['source']])){
|
|||
|
|
$sourceName = $sources[$res['source']]['source'];
|
|||
|
|
}
|
|||
|
|
$sheet->setCellValue($litRow.$row, $sourceName);
|
|||
|
|
}
|
|||
|
|
if($columns['partner'] == 1){
|
|||
|
|
$litRow = $rowsColumnsName['partner'];
|
|||
|
|
$partnerName = '';
|
|||
|
|
if(isset($partners[$res['employee_id']])){
|
|||
|
|
$partnerName = $partners[$res['employee_id']]['name'];
|
|||
|
|
}
|
|||
|
|
$sheet->setCellValue($litRow.$row, $partnerName);
|
|||
|
|
}
|
|||
|
|
if($columns['denial'] == 1 && isset($rowsColumnsName['denial'])) {
|
|||
|
|
$litRow = $rowsColumnsName['denial'];
|
|||
|
|
$denialName = '';
|
|||
|
|
if($res['deleted'] == 1){
|
|||
|
|
if(isset($denials[$res['denial']])){
|
|||
|
|
$denialName = $denials[$res['denial']]['name'];
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
$sheet->setCellValue($litRow.$row, $denialName);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
if($columns['denial_comm'] == 1 && isset($rowsColumnsName['denial_comm'])) {
|
|||
|
|
$litRow = $rowsColumnsName['denial_comm'];
|
|||
|
|
$reason = $res['reason'];
|
|||
|
|
if(isset($denialName)){
|
|||
|
|
$reason = str_replace($denialName.'.','',$reason);
|
|||
|
|
}
|
|||
|
|
$sheet->setCellValue($litRow.$row, $reason);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
if($columns['summa'] == 1 && ($filter->stages->hit == 1 && $raport_id == 0)) {
|
|||
|
|
$litRow = $rowsColumnsName['summa'];
|
|||
|
|
|
|||
|
|
$sheet->setCellValue($litRow.$row, $res['summa']);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
foreach($columnsCastom as $key => $column){
|
|||
|
|
if($column['val'] == 1){
|
|||
|
|
$fieldId = str_replace('column','',$key);
|
|||
|
|
$value = '';
|
|||
|
|
$litRow = $rowsColumnsName[$key];
|
|||
|
|
if(isset($castom_data[$res['id']][$fieldId])){
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
if($Fields[$fieldId]['is_podgroup'] > 0){
|
|||
|
|
|
|||
|
|
foreach($Fields[$fieldId]['podgroups'] as $pd){
|
|||
|
|
if($pd['id'] == $castom_data[$res['id']][$fieldId]['value']){
|
|||
|
|
$value .= $pd['title'];
|
|||
|
|
if(isset($castom_data[$res['id']][$pd['id']]['value'])){
|
|||
|
|
$valTemp = $castom_data[$res['id']][$pd['id']]['value'];
|
|||
|
|
if(isset($pd['params']['items'])){
|
|||
|
|
foreach($pd['params']['items'] as $item){
|
|||
|
|
if($item['value'] == $castom_data[$res['id']][$pd['id']]['value']){
|
|||
|
|
$valTemp = $item['name'];
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
//$val = $pd['params']['items'][$arrFields_models['modelp_'.$value]]['name'];
|
|||
|
|
}
|
|||
|
|
$value .= ": ".$valTemp;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
} else if($Fields[$fieldId]['type'] == 2){
|
|||
|
|
// var_dump($Fields[$fieldId]);
|
|||
|
|
if(isset($Fields[$fieldId]['params']['items'])){
|
|||
|
|
foreach($Fields[$fieldId]['params']['items'] as $item){
|
|||
|
|
if($item['value'] == $castom_data[$res['id']][$fieldId]['value']){
|
|||
|
|
$value = $item['name'];
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
} else {
|
|||
|
|
$value = $castom_data[$res['id']][$fieldId]['value'];
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
// echo $key.' '.$litRow."\n";
|
|||
|
|
$sheet->setCellValue($litRow.$row, $value);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
if (isset($columnsFunnels)){
|
|||
|
|
foreach ($columnsFunnels as $key_cf => $cf){
|
|||
|
|
// var_dump($cf);
|
|||
|
|
if($cf['is_active'] == 1 && isset($funnels[$key_cf])){
|
|||
|
|
$funnelFieldsIds[] = $key_cf;
|
|||
|
|
foreach($cf['steps'] as $key_st => $st){
|
|||
|
|
|
|||
|
|
$nameStep = $steps[str_replace('step','',$key_st)]['name'];
|
|||
|
|
if($st['is_active'] == 1 && isset($eventsReqs[$res['id']])){
|
|||
|
|
foreach($eventsReqs[$res['id']] as $event){
|
|||
|
|
$qwe = explode('«', $event['dop_text'])[1];
|
|||
|
|
$qwe2 = explode('»',$qwe)[0];
|
|||
|
|
if(trim($qwe2) == trim($nameStep)){
|
|||
|
|
$litRow = $rowsColumnsName[$key_st];
|
|||
|
|
$date = '';
|
|||
|
|
if(isset($event['date']) && !empty($event['date'])){
|
|||
|
|
$date = date('d.m.Y', strtotime($event['date']));
|
|||
|
|
}
|
|||
|
|
$sheet->setCellValue($litRow.$row, $date);
|
|||
|
|
$litRow = $rowsColumnsName['who'.$key_st];
|
|||
|
|
$sheet->setCellValue($litRow.$row, $event['user']);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
foreach($st['fields'] as $key_stf => $stf){
|
|||
|
|
if($stf['is_active'] == 1){
|
|||
|
|
$val_stf = '';
|
|||
|
|
if(isset($req_step_values[$res['id']][str_replace('step','',$key_st)][$key_stf])){
|
|||
|
|
$val_stf = $req_step_values[$res['id']][str_replace('step','',$key_st)][$key_stf]['value'];
|
|||
|
|
}
|
|||
|
|
$litRow = $rowsColumnsName[$key_st.'field'.$key_stf];
|
|||
|
|
$sheet->setCellValue($litRow.$row, $val_stf);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
$sheet->getRowDimension($row)->setRowHeight(15);
|
|||
|
|
|
|||
|
|
$row++;
|
|||
|
|
}
|
|||
|
|
}*/
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
$procent = 0;
|
|||
|
|
$row--;
|
|||
|
|
//var_dump($rows);
|
|||
|
|
$procent = ceil($row/$rows*100);
|
|||
|
|
if($cont === false) $procent = 100;
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
//$writer->setPreCalculateFormulas(false);
|
|||
|
|
//if($page == 1){
|
|||
|
|
//$writer->save($_SERVER['DOCUMENT_ROOT'].$name_file);
|
|||
|
|
//}
|
|||
|
|
$spreadsheet->disconnectWorksheets();
|
|||
|
|
$pdo = null;
|
|||
|
|
$rezult['file'] = $name_file;
|
|||
|
|
$rezult['result'] = 'ok';
|
|||
|
|
$rezult['name'] = $name;
|
|||
|
|
$rezult['cont'] = $cont;
|
|||
|
|
$rezult['procent'] = $procent;
|
|||
|
|
$rezult['total'] = count($result);
|
|||
|
|
$rezult['sql'] = $sql_res;
|
|||
|
|
echo json_encode($rezult);
|
|||
|
|
|
|||
|
|
|
|||
|
|
?>
|