463 lines
15 KiB
PHP
463 lines
15 KiB
PHP
<?php
|
||
|
||
require_once($_SERVER['DOCUMENT_ROOT']."/config.php");
|
||
require_once($_SERVER['DOCUMENT_ROOT'].'/vendor/autoload.php');
|
||
|
||
/*if ($_SESSION['id'] == 93) {
|
||
error_reporting(E_ALL & ~E_DEPRECATED & ~E_STRICT & ~E_NOTICE);
|
||
ini_set('display_errors', 1);
|
||
}*/
|
||
|
||
set_time_limit(0);
|
||
ini_set('memory_limit', '8192M');
|
||
|
||
// hide_client_contacts: маска для контактов клиента в Excel-выгрузке заявок.
|
||
$hideContactsExcel = function_exists('should_hide_contacts_for_current_user')
|
||
&& should_hide_contacts_for_current_user();
|
||
|
||
$date = date('d.m.Y H-i-s');
|
||
$name = 'Заявки.xlsx';
|
||
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
||
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
|
||
|
||
$fields = null;
|
||
if (isset($_SESSION['clients_table_fields'])) {
|
||
$unSupported = ['edit', 'eventDate', 'hot'];
|
||
$fields = $_SESSION['clients_table_fields'];
|
||
foreach ($unSupported as $unSet) {
|
||
if (($key = array_search($unSet, $fields)) !== false) {
|
||
unset($fields[$key]);
|
||
}
|
||
}
|
||
}
|
||
|
||
$spreadsheet = new Spreadsheet();
|
||
$sheet = $spreadsheet->getActiveSheet();
|
||
$sheet->setTitle('Заявки');
|
||
$arrEtap = [0=>'', 1=>'Новый клиент', 2=>'В работе', 3=>'Презентация', 4=>'Показ', 5=>'Бронь', 6=>'Подаем на ипотеку', 7=>'Сделка', 8=>'Клиент закрыт'];
|
||
$arrEtapCastom = array();
|
||
|
||
|
||
$arrayHeder = [
|
||
'A' => [
|
||
'name' => "id",
|
||
'title' => '№',
|
||
'width' => 5
|
||
],
|
||
'B' => [
|
||
'name' => "id_req",
|
||
'title' => 'ID',
|
||
'width' => 13
|
||
],
|
||
'C' => [
|
||
'name' => "created_at",
|
||
'title' => 'Дата создания',
|
||
'width' => 13
|
||
],
|
||
'D' => [
|
||
'name' => "user_id",
|
||
'title' => 'Добавил',
|
||
'width' => 35
|
||
],
|
||
'E' => [
|
||
'name' => "who_work",
|
||
'title' => 'Ответственный',
|
||
'width' => 35
|
||
],
|
||
'F' => [
|
||
'name' => "type_id",
|
||
'title' => 'Тип',
|
||
'width' => 20
|
||
],
|
||
'G' => [
|
||
'name' => "name",
|
||
'title' => 'Название',
|
||
'width' => 30
|
||
],
|
||
'H'=> [
|
||
'name' => "fio",
|
||
'title' => 'Клиент',
|
||
'width' => 35
|
||
],
|
||
'I' => [
|
||
'name' => "phone",
|
||
'title' => 'Телефон клиента',
|
||
'width' => 25
|
||
],
|
||
'J' => [
|
||
'name' => "email",
|
||
'title' => 'Email кликнта',
|
||
'width' => 35
|
||
],
|
||
'K' => [
|
||
'name' => "source",
|
||
'title' => 'Рекламный источник',
|
||
'width' => 20
|
||
],
|
||
'L' => [
|
||
'name' => "tags",
|
||
'title' => 'Тег',
|
||
'width' => 20
|
||
],
|
||
'M' => [
|
||
'name' => "opis",
|
||
'title' => 'Описание',
|
||
'width' => 35
|
||
],
|
||
'N' => [
|
||
'name' => "stage",
|
||
'title' => 'Этап',
|
||
'width' => 15
|
||
],
|
||
'O' => [
|
||
'name' => "reason",
|
||
'title' => 'Причина закрытия',
|
||
'width' => 35
|
||
],
|
||
'P' => [
|
||
'name' => "summa",
|
||
'title' => 'Сумма сделки',
|
||
'width' => 20
|
||
],
|
||
'Q' => [
|
||
'name' => "events",
|
||
'title' => 'Последние задачи',
|
||
'width' => 40
|
||
],
|
||
|
||
];//, 'Email' => 35, 'Телефон' => 25, 'Дополнительные телефоны' => 25];
|
||
|
||
foreach ($arrayHeder as $h => $w) {
|
||
$column = false;
|
||
if ($fields && in_array($w['name'], $fields))
|
||
$column = true;
|
||
elseif (!$fields)
|
||
$column = true;
|
||
|
||
if ($column || $w['id']) {
|
||
$sheet->setCellValue($h.'1', $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)->getAlignment()->setWrapText(true);
|
||
$sheet->getStyle($h.'1')->getAlignment()->setWrapText(true);
|
||
}
|
||
}
|
||
|
||
$user = $_SESSION['id'];
|
||
$sql = "SELECT * FROM requisitions WHERE 1 AND (user_id in (select id from users where id=$user or id_manager=$user or id_manager in
|
||
(select id from users where id_manager=$user)) or who_work in (select id from users where id=$user or id_manager=$user or id_manager in
|
||
(select id from users where id_manager=$user))) order by created_at desc";
|
||
if (isset($_SESSION['where_req'])) {
|
||
$where = $_SESSION['where_req'];
|
||
}
|
||
|
||
$sql = $where;
|
||
|
||
$userIds = [];
|
||
$stepIds = [];
|
||
$sourceIds = [];
|
||
$clientIds = [];
|
||
$actIds = [];
|
||
$ids = [];
|
||
|
||
$qTemp = mysql_query($sql);
|
||
while($rTemp = mysql_fetch_assoc($qTemp)) {
|
||
$ids[] = $rTemp['id'];
|
||
if (!in_array($rTemp['user_id'], $userIds)) {
|
||
$userIds[] = $rTemp['user_id'];
|
||
}
|
||
if (!in_array($rTemp['who_work'], $userIds)) {
|
||
$userIds[] = $rTemp['who_work'];
|
||
}
|
||
|
||
if (isset($rTemp['step_id'])) {
|
||
$stepIds[] = $rTemp['step_id'];
|
||
}
|
||
|
||
if (isset($rTemp['source'])) {
|
||
$sourceIds[] = $rTemp['source'];
|
||
}
|
||
if(!empty($rTemp['client_id'])){
|
||
$clientIds[] = $rTemp['client_id'];
|
||
}
|
||
|
||
if(!empty($rTemp['activities'])){
|
||
$acts = json_decode(htmlspecialchars_decode($rTemp['activities']));
|
||
foreach($acts as $idA){
|
||
if (!in_array($idA, $actIds)) {
|
||
$actIds[] = $idA;
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
$users = array();
|
||
$steps = array();
|
||
$sources = array();
|
||
$clients = array();
|
||
$actvts = array();
|
||
$events = array();
|
||
$eventsText = array();
|
||
|
||
$sqlUsers = "SELECT id, IF (fio = '' OR fio IS NULL, CONCAT(last_name, ' ', first_name, ' ', middle_name), fio) AS fio, manager, agency FROM users WHERE id in (" . implode(", ", $userIds) . ")";
|
||
$rezUsers = mysql_query($sqlUsers);
|
||
while ($uz = mysql_fetch_assoc($rezUsers)) {
|
||
$users[$uz['id']] = $uz;
|
||
}
|
||
|
||
if (count($stepIds) > 0) {
|
||
$sqlSteps = "SELECT name, id FROM funnel_steps WHERE id in (" . implode(", ", $stepIds) . ")";
|
||
$qSteps = mysql_query($sqlSteps);
|
||
while($rSteps = mysql_fetch_assoc($qSteps)){
|
||
$arrEtapCastom[$rSteps['id']] = $rSteps['name'];
|
||
}
|
||
}
|
||
|
||
if (count($sourceIds) > 0) {
|
||
$sqlSources = "SELECT `source`, `id` FROM `advertising_sources` WHERE `id` in (" . implode(", ", $sourceIds) . ")";
|
||
$rezSources = mysql_query($sqlSources);
|
||
while ($source = mysql_fetch_assoc($rezSources)) {
|
||
$sources[$source['id']] = $source;
|
||
}
|
||
}
|
||
|
||
if (count($clientIds) > 0) {
|
||
$sqlClients = "SELECT fio, phone, email, id FROM clients WHERE id in (" . implode(", ", $clientIds) . ")";
|
||
$rezClients = mysql_query($sqlClients);
|
||
while ($cl = mysql_fetch_assoc($rezClients)) {
|
||
$clients[$cl['id']] = $cl;
|
||
}
|
||
}
|
||
|
||
if (count($actIds) > 0) {
|
||
$sqlActs = "SELECT id, name FROM `activities` WHERE id in (" . implode(", ", $actIds) . ")";
|
||
$rezActs = mysql_query($sqlActs);
|
||
while ($act = mysql_fetch_assoc($rezActs)) {
|
||
$actvts[$act['id']] = $act;
|
||
}
|
||
}
|
||
|
||
$sql_event = "SELECT * FROM user_client_events WHERE req_id>0 and req_id in (".implode(',',$ids).") and cancel=0 AND type != 'step' AND type != 'file' AND type !='show_object' order by id desc";
|
||
$q_event = mysql_query($sql_event);
|
||
while($r_event = mysql_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 = '';
|
||
foreach($event as $ide => $ev){
|
||
if($e_i < 3) {
|
||
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;
|
||
}
|
||
|
||
//var_dump($eventsText);
|
||
|
||
$q = mysql_query($sql);
|
||
$i = 2;
|
||
$k = 1;
|
||
|
||
$sql_type = "SELECT * FROM `requisitions_type`";
|
||
$q_type = mysql_query($sql_type);
|
||
$types = array();
|
||
while($r_type = mysql_fetch_assoc($q_type)){
|
||
$types[$r_type['id']] = $r_type['name'];
|
||
}
|
||
|
||
while($r = mysql_fetch_assoc($q)) {
|
||
$sheet->setCellValue('A'.$i, $k);
|
||
|
||
if ($fields && in_array('id_req', $fields))
|
||
$sheet->setCellValue('B'.$i, $r['id']);
|
||
elseif (!$fields)
|
||
$sheet->setCellValue('B'.$i,$r['id']);
|
||
|
||
|
||
if ($fields && in_array('name', $fields))
|
||
$sheet->setCellValue('F'.$i, $r['name']);
|
||
elseif (!$fields)
|
||
$sheet->setCellValue('F'.$i, $r['name']);
|
||
|
||
if ($fields && in_array('type_id', $fields)){
|
||
|
||
$sheet->setCellValue('G'.$i, $types[$r['type_id']]);
|
||
}
|
||
elseif (!$fields) {
|
||
$sheet->setCellValue('G'.$i, $types[$r['type_id']]);
|
||
}
|
||
|
||
if ($fields && in_array('created_at', $fields))
|
||
$sheet->setCellValue('C'.$i, date('d.m.Y H:i:s', strtotime($r['created_at'])));
|
||
elseif (!$fields)
|
||
$sheet->setCellValue('C'.$i, date('d.m.Y H:i:s', strtotime($r['created_at'])));
|
||
|
||
$stages = (int)$r['stage'];
|
||
if((int)$r['deleted'] == 1) $stages = 8;
|
||
|
||
if($r['funnel_id'] == 0 || $stages == 8) {
|
||
|
||
if ($fields && in_array('stage', $fields))
|
||
$sheet->setCellValue('N'.$i, $arrEtap[$stages]);
|
||
elseif (!$fields)
|
||
$sheet->setCellValue('N'.$i, $arrEtap[$stages]);
|
||
|
||
} else {
|
||
$name_etap = "";
|
||
|
||
if(isset($arrEtapCastom[$r['step_id']])){
|
||
$name_etap = $arrEtapCastom[$r['step_id']];
|
||
}
|
||
|
||
if ($fields && in_array('stage', $fields))
|
||
$sheet->setCellValue('N'.$i, $name_etap);
|
||
elseif (!$fields)
|
||
$sheet->setCellValue('N'.$i, $name_etap);
|
||
|
||
}
|
||
|
||
$source = "";
|
||
if($r['source'] > 0 && isset($sources[$r['source']])){
|
||
$source = $sources[$r['source']]['source'];
|
||
}
|
||
|
||
if ($fields && in_array('source', $fields))
|
||
$sheet->setCellValue('J'.$i, $source);
|
||
elseif (!$fields)
|
||
$sheet->setCellValue('J'.$i, $source);
|
||
|
||
if(!empty($r['activities'])){
|
||
$activities = '';
|
||
$arrActive = json_decode(htmlspecialchars_decode($r['activities']));
|
||
foreach($arrActive as $idA){
|
||
if (isset($actvts[$idA])) {
|
||
$name = $actvts[$idA]['name'];
|
||
if ($activities != '') $activities .= ', ';
|
||
$activities .= $name;
|
||
}
|
||
}
|
||
$sheet->setCellValue('K'.$i, $activities);
|
||
}
|
||
|
||
if ($fields && in_array('opis', $fields))
|
||
$sheet->setCellValue('L'.$i, strip_tags(htmlspecialchars_decode($r['description'])));
|
||
elseif (!$fields)
|
||
$sheet->setCellValue('L'.$i, strip_tags(htmlspecialchars_decode($r['description'])));
|
||
|
||
if ($fields && in_array('reason', $fields))
|
||
$sheet->setCellValue('O'.$i, strip_tags($r['reason']));
|
||
elseif (!$fields)
|
||
$sheet->setCellValue('O'.$i, strip_tags($r['reason']));
|
||
|
||
if ($fields && in_array('summa', $fields))
|
||
$sheet->setCellValue('P'.$i, $r['summa']);
|
||
elseif (!$fields)
|
||
$sheet->setCellValue('P'.$i, $r['summa']);
|
||
|
||
$agent = 'Агент';
|
||
$manager = 'Агент';
|
||
if(isset($users[$r['user_id']])){
|
||
$r_user = $users[$r['user_id']];
|
||
if($r_user['agency'] == 1) $agent = 'Агенство '.$r_user['fio'];
|
||
elseif($r_user['manager'] == 1) $agent = 'Менеджер '.$r_user['fio'];
|
||
else $agent .= ' '.$r_user['fio'];
|
||
} else {
|
||
$sql_user = "SELECT fio, manager, agency FROM users WHERE id = ".$r['user_id'];
|
||
$r_user = mysql_fetch_assoc(mysql_query($sql_user));
|
||
if($r_user['agency'] == 1) $agent = 'Агенство '.$r_user['fio'];
|
||
elseif($r_user['manager'] == 1) $agent = 'Менеджер '.$r_user['fio'];
|
||
else $agent .= ' '.$r_user['fio'];
|
||
$users[$r['user_id']] = $agent;
|
||
}
|
||
|
||
if ($fields && in_array('user_id', $fields))
|
||
$sheet->setCellValue('D'.$i, htmlspecialchars_decode($agent));
|
||
elseif (!$fields)
|
||
$sheet->setCellValue('D'.$i, htmlspecialchars_decode($agent));
|
||
|
||
if(isset($users[$r['who_work']])){
|
||
$r_user = $users[$r['who_work']];
|
||
if($r_user['agency'] == 1) $manager = 'Агенство '.$r_user['fio'];
|
||
elseif($r_user['manager'] == 1) $manager = 'Менеджер '.$r_user['fio'];
|
||
else $manager .= ' '.$r_user['fio'];
|
||
} elseif($r['who_work'] == 0){
|
||
$manager = "Всё агенство";
|
||
$users[$r['who_work']] = $manager;
|
||
} else {
|
||
$sql_user = "SELECT fio, manager, agency FROM users WHERE id = ".$r['who_work'];
|
||
$r_user = mysql_fetch_assoc(mysql_query($sql_user));
|
||
if($r_user['agency'] == 1) $manager = 'Агенство '.$r_user['fio'];
|
||
elseif($r_user['manager'] == 1) $manager = 'Менеджер '.$r_user['fio'];
|
||
else $manager .= ' '.$r_user['fio'];
|
||
$users[$r['who_work']] = $manager;
|
||
}
|
||
|
||
if ($fields && in_array('who_work', $fields))
|
||
$sheet->setCellValue('E'.$i, htmlspecialchars_decode($manager));
|
||
elseif (!$fields)
|
||
$sheet->setCellValue('E'.$i, htmlspecialchars_decode($manager));
|
||
|
||
if(!empty($r['client_id']) && isset($clients[$r['client_id']])){
|
||
$r_cl = $clients[$r['client_id']];
|
||
$fioCell = $hideContactsExcel ? mask_fio_if_contains_phone($r_cl['fio']) : $r_cl['fio'];
|
||
$phoneCell = $hideContactsExcel
|
||
? mask_client_phone($r_cl['phone'])
|
||
: (substr($r_cl['phone'], 0, 2)."(".substr($r_cl['phone'], 2, 3).")".substr($r_cl['phone'], 5, 3)."-".substr($r_cl['phone'], 8, 2)."-".substr($r_cl['phone'], 10, 2));
|
||
$emailCell = $hideContactsExcel ? mask_client_email($r_cl['email']) : $r_cl['email'];
|
||
$sheet->setCellValue('H'.$i, htmlspecialchars_decode($fioCell));
|
||
$sheet->setCellValue('I'.$i, $phoneCell);
|
||
$sheet->setCellValue('J'.$i, $emailCell);
|
||
}
|
||
//var_dump($eventsText[$r['id']]);
|
||
if(isset($eventsText[$r['id']])){
|
||
$sheet->setCellValue('Q'.$i, htmlspecialchars_decode($eventsText[$r['id']]));
|
||
}
|
||
|
||
|
||
|
||
$i++;
|
||
$k++;
|
||
}
|
||
|
||
/*$user = new User;
|
||
$user->get($_SESSION['id']);
|
||
if($user->agencyId == 6543) {
|
||
$sheet->removeColumn('L');
|
||
$sheet->removeColumn('K');
|
||
$sheet->removeColumn('E');
|
||
$sheet->removeColumn('C');
|
||
$sheet->removeColumn('A');
|
||
}*/
|
||
//if($_SESSION['id'] != 5238){
|
||
$writer = new Xlsx($spreadsheet);
|
||
|
||
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
|
||
header('Content-Disposition: attachment;filename="Заявки_'.$date.'.xlsx"');
|
||
header('Cache-Control: max-age=0');
|
||
|
||
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
|
||
$writer->save('php://output');
|
||
//}
|