674 lines
22 KiB
PHP
674 lines
22 KiB
PHP
|
<?php
|
|||
|
|
set_time_limit(0);
|
|||
|
|
ini_set('memory_limit', '8192M');
|
|||
|
|
require_once($_SERVER['DOCUMENT_ROOT']."/config.php");
|
|||
|
|
require_once($_SERVER['DOCUMENT_ROOT'].'/vendor/autoload.php');
|
|||
|
|
|
|||
|
|
// hide_client_contacts: проверяем один раз, дальше маскируем поля fio/email/phone/phone2
|
|||
|
|
// в каждой строке выгрузки и заменяем substr-форматирование "+7(912)420-71-50" на нашу
|
|||
|
|
// маску "+X *** *** ** **", чтобы экспорт в 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);*/
|
|||
|
|
|
|||
|
|
$date = date('d.m.Y H-i-s');
|
|||
|
|
$name = 'clients.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();
|
|||
|
|
$sqlSteps = "SELECT id, name FROM funnel_steps";
|
|||
|
|
$qSteps = mysql_query($sqlSteps);
|
|||
|
|
while($rSteps = mysql_fetch_assoc($qSteps)){
|
|||
|
|
$arrEtapCastom[$rSteps['id']] = $rSteps['name'];
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$arrParam = [];
|
|||
|
|
if (in_array("id_client", $fields)) {
|
|||
|
|
$arr = [
|
|||
|
|
'name' => "id_client",
|
|||
|
|
'title' => 'ID',
|
|||
|
|
'width' => 10
|
|||
|
|
];
|
|||
|
|
$arrParam[] = $arr;
|
|||
|
|
}
|
|||
|
|
if (in_array("fio", $fields)) {
|
|||
|
|
$arr = [
|
|||
|
|
'name' => "fio",
|
|||
|
|
'title' => 'ФИО',
|
|||
|
|
'width' => 30
|
|||
|
|
];
|
|||
|
|
$arrParam[] = $arr;
|
|||
|
|
}
|
|||
|
|
if (in_array("email", $fields)) {
|
|||
|
|
$arr = [
|
|||
|
|
'name' => "email",
|
|||
|
|
'title' => 'Email',
|
|||
|
|
'width' => 35
|
|||
|
|
];
|
|||
|
|
$arrParam[] = $arr;
|
|||
|
|
}
|
|||
|
|
if (in_array("phone", $fields)) {
|
|||
|
|
$arr = [
|
|||
|
|
'name' => "phone",
|
|||
|
|
'title' => 'Телефон',
|
|||
|
|
'width' => 25
|
|||
|
|
];
|
|||
|
|
$arrParam[] = $arr;
|
|||
|
|
}
|
|||
|
|
if (in_array("phone2", $fields)) {
|
|||
|
|
$arr = [
|
|||
|
|
'name' => "phone2",
|
|||
|
|
'title' => 'Дополнительные телефоны',
|
|||
|
|
'width' => 25
|
|||
|
|
];
|
|||
|
|
$arrParam[] = $arr;
|
|||
|
|
}
|
|||
|
|
if (in_array("date_add", $fields)) {
|
|||
|
|
$arr = [
|
|||
|
|
'name' => "date_add",
|
|||
|
|
'title' => 'Дата создания',
|
|||
|
|
'width' => 13
|
|||
|
|
];
|
|||
|
|
$arrParam[] = $arr;
|
|||
|
|
}
|
|||
|
|
if (in_array("stage", $fields)) {
|
|||
|
|
$arr = [
|
|||
|
|
'name' => "stage",
|
|||
|
|
'title' => 'Этап',
|
|||
|
|
'width' => 15
|
|||
|
|
];
|
|||
|
|
$arrParam[] = $arr;
|
|||
|
|
}
|
|||
|
|
if (in_array("source", $fields)) {
|
|||
|
|
$arr = [
|
|||
|
|
'name' => "source",
|
|||
|
|
'title' => 'Рекламный источник',
|
|||
|
|
'width' => 20
|
|||
|
|
];
|
|||
|
|
$arrParam[] = $arr;
|
|||
|
|
}
|
|||
|
|
if (in_array("opis", $fields)) {
|
|||
|
|
$arr = [
|
|||
|
|
'name' => "opis",
|
|||
|
|
'title' => 'Описание',
|
|||
|
|
'width' => 35
|
|||
|
|
];
|
|||
|
|
$arrParam[] = $arr;
|
|||
|
|
}
|
|||
|
|
if (in_array("reason", $fields)) {
|
|||
|
|
$arr = [
|
|||
|
|
'name' => "reason",
|
|||
|
|
'title' => 'Причина закрытия',
|
|||
|
|
'width' => 35
|
|||
|
|
];
|
|||
|
|
$arrParam[] = $arr;
|
|||
|
|
}
|
|||
|
|
if (in_array("summa", $fields)) {
|
|||
|
|
$arr = [
|
|||
|
|
'name' => "summa",
|
|||
|
|
'title' => 'Сумма сделки',
|
|||
|
|
'width' => 20
|
|||
|
|
];
|
|||
|
|
$arrParam[] = $arr;
|
|||
|
|
}
|
|||
|
|
if (in_array("id_agent", $fields)) {
|
|||
|
|
$arr = [
|
|||
|
|
'name' => "id_agent",
|
|||
|
|
'title' => 'Добавил',
|
|||
|
|
'width' => 35
|
|||
|
|
];
|
|||
|
|
$arrParam[] = $arr;
|
|||
|
|
}
|
|||
|
|
if (in_array("role", $fields)) {
|
|||
|
|
$arr = [
|
|||
|
|
'name' => "id",
|
|||
|
|
'title' => '№',
|
|||
|
|
'width' => 5
|
|||
|
|
];
|
|||
|
|
$arrParam[] = $arr;
|
|||
|
|
}
|
|||
|
|
if (in_array("who_work", $fields)) {
|
|||
|
|
$arr = [
|
|||
|
|
'name' => "who_work",
|
|||
|
|
'title' => 'Ответственный',
|
|||
|
|
'width' => 35
|
|||
|
|
];
|
|||
|
|
$arrParam[] = $arr;
|
|||
|
|
}
|
|||
|
|
if (in_array("budget", $fields)) {
|
|||
|
|
$arr = [
|
|||
|
|
'name' => "budget",
|
|||
|
|
'title' => 'Бюджет',
|
|||
|
|
'width' => 20
|
|||
|
|
];
|
|||
|
|
$arrParam[] = $arr;
|
|||
|
|
}
|
|||
|
|
if (in_array("role", $fields)) {
|
|||
|
|
$arr = [
|
|||
|
|
'name' => "role",
|
|||
|
|
'title' => 'Роль клиента',
|
|||
|
|
'width' => 20
|
|||
|
|
];
|
|||
|
|
$arrParam[] = $arr;
|
|||
|
|
}
|
|||
|
|
if (in_array("events", $fields)) {
|
|||
|
|
$arr = [
|
|||
|
|
'name' => "events",
|
|||
|
|
'title' => 'Последние задачи',
|
|||
|
|
'width' => 40
|
|||
|
|
];
|
|||
|
|
$arrParam[] = $arr;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
$arrayHeder = [
|
|||
|
|
'A' => [
|
|||
|
|
'name' => "id",
|
|||
|
|
'title' => '№',
|
|||
|
|
'width' => 5
|
|||
|
|
],
|
|||
|
|
'B' => [
|
|||
|
|
'name' => "id_client",
|
|||
|
|
'title' => 'ID',
|
|||
|
|
'width' => 10
|
|||
|
|
],
|
|||
|
|
'C' => [
|
|||
|
|
'name' => "fio",
|
|||
|
|
'title' => 'ФИО',
|
|||
|
|
'width' => 30
|
|||
|
|
],
|
|||
|
|
'D' => [
|
|||
|
|
'name' => "email",
|
|||
|
|
'title' => 'Email',
|
|||
|
|
'width' => 35
|
|||
|
|
],
|
|||
|
|
'E' => [
|
|||
|
|
'name' => "phone",
|
|||
|
|
'title' => 'Телефон',
|
|||
|
|
'width' => 25
|
|||
|
|
],
|
|||
|
|
'F' => [
|
|||
|
|
'name' => "phone2",
|
|||
|
|
'title' => 'Дополнительные телефоны',
|
|||
|
|
'width' => 25
|
|||
|
|
],
|
|||
|
|
'G' => [
|
|||
|
|
'name' => "date_add",
|
|||
|
|
'title' => 'Дата создания',
|
|||
|
|
'width' => 13
|
|||
|
|
],
|
|||
|
|
'H' => [
|
|||
|
|
'name' => "stage",
|
|||
|
|
'title' => 'Этап',
|
|||
|
|
'width' => 15
|
|||
|
|
],
|
|||
|
|
'I' => [
|
|||
|
|
'name' => "source",
|
|||
|
|
'title' => 'Рекламный источник',
|
|||
|
|
'width' => 20
|
|||
|
|
],
|
|||
|
|
'J' => [
|
|||
|
|
'name' => "opis",
|
|||
|
|
'title' => 'Описание',
|
|||
|
|
'width' => 35
|
|||
|
|
],
|
|||
|
|
'K' => [
|
|||
|
|
'name' => "reason",
|
|||
|
|
'title' => 'Причина закрытия',
|
|||
|
|
'width' => 35
|
|||
|
|
],
|
|||
|
|
'L' => [
|
|||
|
|
'name' => "summa",
|
|||
|
|
'title' => 'Сумма сделки',
|
|||
|
|
'width' => 20
|
|||
|
|
],
|
|||
|
|
'M' => [
|
|||
|
|
'name' => "id_agent",
|
|||
|
|
'title' => 'Добавил',
|
|||
|
|
'width' => 35
|
|||
|
|
],
|
|||
|
|
'N' => [
|
|||
|
|
'name' => "who_work",
|
|||
|
|
'title' => 'Ответственный',
|
|||
|
|
'width' => 35
|
|||
|
|
],
|
|||
|
|
'O' => [
|
|||
|
|
'name' => "budget",
|
|||
|
|
'title' => 'Бюджет',
|
|||
|
|
'width' => 20
|
|||
|
|
],
|
|||
|
|
'P' => [
|
|||
|
|
'name' => "role",
|
|||
|
|
'title' => 'Роль клиента',
|
|||
|
|
'width' => 20
|
|||
|
|
],
|
|||
|
|
'Q' => [
|
|||
|
|
'name' => "events",
|
|||
|
|
'title' => 'Последние задачи',
|
|||
|
|
'width' => 40
|
|||
|
|
],
|
|||
|
|
'R' => [
|
|||
|
|
'name' => "events_call",
|
|||
|
|
'title' => 'Касания/Звонки',
|
|||
|
|
'width' => 40
|
|||
|
|
],
|
|||
|
|
'S' => [
|
|||
|
|
'name' => "contract_type",
|
|||
|
|
'title' => 'Тип договора',
|
|||
|
|
'width' => 40
|
|||
|
|
],
|
|||
|
|
'T' => [
|
|||
|
|
'name' => "contract_number",
|
|||
|
|
'title' => 'Номер договора',
|
|||
|
|
'width' => 40
|
|||
|
|
],
|
|||
|
|
'U' => [
|
|||
|
|
'name' => "contract_date",
|
|||
|
|
'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);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$where = "AND (id_agent = ".$_SESSION['id']." OR who_work = ".$_SESSION['id'].") AND `date_add` > '1970-01-01' and deleted <> '1' and confirm > 0 ORDER BY if ((SELECT date_update from events_clients where client_id=clients.id and event = 'accepted' order by id desc limit 1) > clients.date_add, (SELECT date_update from events_clients where client_id=clients.id and event = 'accepted' order by id desc limit 1) ,clients.date_add) desc";
|
|||
|
|
if (isset($_SESSION['where_clients'])) {
|
|||
|
|
$where = $_SESSION['where_clients'];
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$sql = "SELECT *, DATE_FORMAT(date_add, '%d.%m.%Y %H:%i:%s') AS date_add FROM clients WHERE 1 ".$where;
|
|||
|
|
$q = mysql_query($sql);
|
|||
|
|
$i = 2;
|
|||
|
|
$k = 1;
|
|||
|
|
$ids = array();
|
|||
|
|
$users = array();
|
|||
|
|
while($r = mysql_fetch_assoc($q)) {
|
|||
|
|
$ids[] = $r['id'];
|
|||
|
|
|
|||
|
|
// Готовим значения с учётом hide_client_contacts: маскируем fio/email/phone,
|
|||
|
|
// а для основного телефона и phone2 заменяем substr-форматирование "+7(912)420-71-50"
|
|||
|
|
// на нашу маску "+X *** *** ** **".
|
|||
|
|
$fioOut = $hideContactsExcel ? mask_fio_if_contains_phone($r['fio']) : $r['fio'];
|
|||
|
|
$emailOut = $hideContactsExcel ? mask_client_email($r['email']) : $r['email'];
|
|||
|
|
$phoneOut = $hideContactsExcel
|
|||
|
|
? mask_client_phone($r['phone'])
|
|||
|
|
: (substr($r['phone'], 0, 2)."(".substr($r['phone'], 2, 3).")".substr($r['phone'], 5, 3)."-".substr($r['phone'], 8, 2)."-".substr($r['phone'], 10, 2));
|
|||
|
|
|
|||
|
|
$sheet->setCellValue('A'.$i, $k);
|
|||
|
|
|
|||
|
|
if ($fields && in_array('id_client', $fields))
|
|||
|
|
$sheet->setCellValue('B'.$i, $r['id']);
|
|||
|
|
elseif (!$fields)
|
|||
|
|
$sheet->setCellValue('B'.$i, $r['id']);
|
|||
|
|
|
|||
|
|
if ($fields && in_array('fio', $fields))
|
|||
|
|
$sheet->setCellValue('C'.$i, $fioOut);
|
|||
|
|
elseif (!$fields)
|
|||
|
|
$sheet->setCellValue('C'.$i, $fioOut);
|
|||
|
|
|
|||
|
|
if ($fields && in_array('email', $fields))
|
|||
|
|
$sheet->setCellValue('D'.$i, $emailOut);
|
|||
|
|
elseif (!$fields)
|
|||
|
|
$sheet->setCellValue('D'.$i, $emailOut);
|
|||
|
|
|
|||
|
|
if ($fields && in_array('phone', $fields))
|
|||
|
|
$sheet->setCellValue('E'.$i, $phoneOut);
|
|||
|
|
elseif (!$fields)
|
|||
|
|
$sheet->setCellValue('E'.$i, $phoneOut);
|
|||
|
|
|
|||
|
|
$phoneStr = '';
|
|||
|
|
$phone2 = json_decode(html_entity_decode($r['phone2']), true);
|
|||
|
|
if(count($phone2) > 0){
|
|||
|
|
foreach($phone2 as $ph){
|
|||
|
|
if($phoneStr != '') $phoneStr .= ' ';
|
|||
|
|
$phoneStr .= $hideContactsExcel
|
|||
|
|
? mask_client_phone($ph)
|
|||
|
|
: (substr($ph, 0, 2)."(".substr($ph, 2, 3).")".substr($ph, 5, 3)."-".substr($ph, 8, 2)."-".substr($ph, 10, 2));
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if ($fields && in_array('phone2', $fields))
|
|||
|
|
$sheet->setCellValue('F'.$i, $phoneStr);
|
|||
|
|
elseif (!$fields)
|
|||
|
|
$sheet->setCellValue('F'.$i, $phoneStr);
|
|||
|
|
|
|||
|
|
if ($fields && in_array('date_add', $fields))
|
|||
|
|
$sheet->setCellValue('G'.$i, $r['date_add']);
|
|||
|
|
elseif (!$fields)
|
|||
|
|
$sheet->setCellValue('G'.$i, $r['date_add']);
|
|||
|
|
|
|||
|
|
$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('H'.$i, $arrEtap[$stages]);
|
|||
|
|
elseif (!$fields)
|
|||
|
|
$sheet->setCellValue('H'.$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('H'.$i, $name_etap);
|
|||
|
|
elseif (!$fields)
|
|||
|
|
$sheet->setCellValue('H'.$i, $name_etap);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$source = "";
|
|||
|
|
$sql_source = "SELECT `source` FROM `advertising_sources` WHERE `id` = '{$r['source']}'";
|
|||
|
|
$q_source = mysql_query($sql_source);
|
|||
|
|
$r_source = mysql_fetch_assoc($q_source);
|
|||
|
|
$source = $r_source['source'];
|
|||
|
|
|
|||
|
|
if ($fields && in_array('source', $fields))
|
|||
|
|
$sheet->setCellValue('I'.$i, $source);
|
|||
|
|
elseif (!$fields)
|
|||
|
|
$sheet->setCellValue('I'.$i, $source);
|
|||
|
|
|
|||
|
|
if ($fields && in_array('opis', $fields))
|
|||
|
|
$sheet->setCellValue('J'.$i, strip_tags(str_replace('=','',$r['opis'])));
|
|||
|
|
elseif (!$fields)
|
|||
|
|
|
|||
|
|
$sheet->setCellValue('J'.$i, strip_tags(str_replace('=','',$r['opis'])));
|
|||
|
|
|
|||
|
|
if ($fields && in_array('reason', $fields))
|
|||
|
|
$sheet->setCellValue('K'.$i, strip_tags($r['reason']));
|
|||
|
|
elseif (!$fields)
|
|||
|
|
$sheet->setCellValue('K'.$i, strip_tags($r['reason']));
|
|||
|
|
|
|||
|
|
if ($fields && in_array('summa', $fields))
|
|||
|
|
$sheet->setCellValue('L'.$i, $r['summa']);
|
|||
|
|
elseif (!$fields)
|
|||
|
|
$sheet->setCellValue('L'.$i, $r['summa']);
|
|||
|
|
|
|||
|
|
$agent = 'Агент';
|
|||
|
|
$manager = 'Агент';
|
|||
|
|
if(isset($users[$r['id_agent']])){
|
|||
|
|
$agent = $users[$r['id_agent']];
|
|||
|
|
} else {
|
|||
|
|
$sql_user = "SELECT CONCAT(last_name, ' ', first_name, ' ', middle_name) as fio, manager, agency FROM users WHERE id = ".$r['id_agent'] .
|
|||
|
|
" UNION SELECT CONCAT(last_name, ' ', first_name, ' ', middle_name, ' (удален)') as fio, manager, agency FROM users_delete WHERE user_id = {$r['id_agent']}";
|
|||
|
|
$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['id_agent']] = $agent;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if ($fields && in_array('id_agent', $fields))
|
|||
|
|
$sheet->setCellValue('M'.$i, $agent);
|
|||
|
|
elseif (!$fields)
|
|||
|
|
$sheet->setCellValue('M'.$i, $agent);
|
|||
|
|
|
|||
|
|
if(isset($users[$r['who_work']])){
|
|||
|
|
$manager = $users[$r['who_work']];
|
|||
|
|
} elseif($r['who_work'] == 0){
|
|||
|
|
$manager = "Всё агенство";
|
|||
|
|
$users[$r['who_work']] = $manager;
|
|||
|
|
} else {
|
|||
|
|
$sql_user = "SELECT CONCAT(last_name, ' ', first_name, ' ', middle_name) as fio, manager, agency FROM users WHERE id = ".$r['who_work'] .
|
|||
|
|
" UNION SELECT CONCAT(last_name, ' ', first_name, ' ', middle_name, ' (удален)') as fio, manager, agency FROM users_delete WHERE user_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('N'.$i, $manager);
|
|||
|
|
elseif (!$fields)
|
|||
|
|
$sheet->setCellValue('N'.$i, $manager);
|
|||
|
|
|
|||
|
|
if ($fields && in_array('budget', $fields))
|
|||
|
|
$sheet->setCellValue('O'.$i, $r['budget']);
|
|||
|
|
elseif (!$fields)
|
|||
|
|
$sheet->setCellValue('O'.$i, $r['budget']);
|
|||
|
|
|
|||
|
|
$role = '';
|
|||
|
|
$roles = json_decode(html_entity_decode($r['role']), true);
|
|||
|
|
if(count($roles) > 0){
|
|||
|
|
foreach($roles as $rol){
|
|||
|
|
if($role != '') $role .= ', ';
|
|||
|
|
$role .= $rol;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if ($fields && in_array('role', $fields))
|
|||
|
|
$sheet->setCellValue('P'.$i, $role);
|
|||
|
|
elseif (!$fields)
|
|||
|
|
$sheet->setCellValue('P'.$i, $role);
|
|||
|
|
|
|||
|
|
$i++;
|
|||
|
|
$k++;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$events = array();
|
|||
|
|
$eventsText = array();
|
|||
|
|
$eventsCallText = array();
|
|||
|
|
$i = 2;
|
|||
|
|
$k = 1;
|
|||
|
|
|
|||
|
|
$sql_event = "SELECT * FROM user_client_events WHERE client_id>0 and client_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['client_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;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
//var_dump($eventsText);
|
|||
|
|
|
|||
|
|
|
|||
|
|
foreach($ids as $cid){
|
|||
|
|
if(isset($eventsText[$cid])){
|
|||
|
|
$sheet->setCellValue('Q'.$i, htmlspecialchars_decode($eventsText[$cid]));
|
|||
|
|
$sheet->setCellValue('R'.$i, htmlspecialchars_decode($eventsCallText[$cid]));
|
|||
|
|
}
|
|||
|
|
$i++;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//Договоры
|
|||
|
|
$sql_contracts = "SELECT *, (SELECT type FROM contract_types WHERE id=contracts_client.type) as type_name FROM `contracts_client` WHERE client_id in (".implode(',', $ids).")";
|
|||
|
|
$q_contract = mysql_query($sql_contracts);
|
|||
|
|
while($contract = mysql_fetch_assoc($q_contract)){
|
|||
|
|
if(!isset($contracts[$contract['client_id']])){
|
|||
|
|
$contracts[$contract['client_id']] = Array('types'=>'', 'numbers'=>'', 'dates'=>'');
|
|||
|
|
}
|
|||
|
|
if($contracts[$contract['client_id']]['types'] != '') $contracts[$contract['client_id']]['types'] .= '; ';
|
|||
|
|
if($contracts[$contract['client_id']]['numbers'] != '') $contracts[$contract['client_id']]['numbers'] .= '; ';
|
|||
|
|
if($contracts[$contract['client_id']]['dates'] != '') $contracts[$contract['client_id']]['dates'] .= '; ';
|
|||
|
|
$contracts[$contract['client_id']]['types'] .= $contract['type_name'];
|
|||
|
|
$contracts[$contract['client_id']]['numbers'] .= '№'.$contract['number'];
|
|||
|
|
$contracts[$contract['client_id']]['dates'] .= date('d.m.Y',$contract['date_start']).' - '.date('d.m.Y',$contract['date_end']);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$i = 2;
|
|||
|
|
$k = 1;
|
|||
|
|
|
|||
|
|
foreach($ids as $cid){
|
|||
|
|
if(isset($contracts[$cid])){
|
|||
|
|
$sheet->setCellValue('S'.$i, htmlspecialchars_decode($contracts[$cid]['types']));
|
|||
|
|
$sheet->setCellValue('T'.$i, htmlspecialchars_decode($contracts[$cid]['numbers']));
|
|||
|
|
$sheet->setCellValue('U'.$i, htmlspecialchars_decode($contracts[$cid]['dates']));
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
$i++;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$user = new User;
|
|||
|
|
$user->get($_SESSION['id']);
|
|||
|
|
if($user->agencyId == 6543) {
|
|||
|
|
$sheet->removeColumn('M');
|
|||
|
|
$sheet->removeColumn('L');
|
|||
|
|
$sheet->removeColumn('F');
|
|||
|
|
$sheet->removeColumn('D');
|
|||
|
|
$sheet->removeColumn('A');
|
|||
|
|
}
|
|||
|
|
// if (!in_array("events", $fields)) {
|
|||
|
|
// $sheet->removeColumn('Q');
|
|||
|
|
// }
|
|||
|
|
// if (!in_array("role", $fields)) {
|
|||
|
|
// $sheet->removeColumn('P');
|
|||
|
|
// }
|
|||
|
|
if($fields){
|
|||
|
|
if (!in_array("budget", $fields)) {
|
|||
|
|
$sheet->removeColumn('O');
|
|||
|
|
}
|
|||
|
|
if (!in_array("who_work", $fields)) {
|
|||
|
|
$sheet->removeColumn('N');
|
|||
|
|
}
|
|||
|
|
if (!in_array("id_agent", $fields)) {
|
|||
|
|
$sheet->removeColumn('M');
|
|||
|
|
}
|
|||
|
|
if (!in_array("summa", $fields)) {
|
|||
|
|
$sheet->removeColumn('L');
|
|||
|
|
}
|
|||
|
|
if (!in_array("reason", $fields)) {
|
|||
|
|
$sheet->removeColumn('K');
|
|||
|
|
}
|
|||
|
|
if (!in_array("opis", $fields)) {
|
|||
|
|
$sheet->removeColumn('J');
|
|||
|
|
}
|
|||
|
|
if (!in_array("source", $fields)) {
|
|||
|
|
$sheet->removeColumn('I');
|
|||
|
|
}
|
|||
|
|
if (!in_array("stage", $fields)) {
|
|||
|
|
$sheet->removeColumn('H');
|
|||
|
|
}
|
|||
|
|
if (!in_array("date_add", $fields)) {
|
|||
|
|
$sheet->removeColumn('G');
|
|||
|
|
}
|
|||
|
|
if (!in_array("phone2", $fields)) {
|
|||
|
|
$sheet->removeColumn('F');
|
|||
|
|
}
|
|||
|
|
if (!in_array("phone", $fields)) {
|
|||
|
|
$sheet->removeColumn('E');
|
|||
|
|
}
|
|||
|
|
if (!in_array("email", $fields)) {
|
|||
|
|
$sheet->removeColumn('D');
|
|||
|
|
}
|
|||
|
|
if (!in_array("fio", $fields)) {
|
|||
|
|
$sheet->removeColumn('C');
|
|||
|
|
}
|
|||
|
|
if (!in_array("id_client", $fields)) {
|
|||
|
|
$sheet->removeColumn('B');
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$sheet->removeColumnByIndex(count($fields)+3);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//if($_SESSION['id'] != 5238){
|
|||
|
|
$writer = new Xlsx($spreadsheet);
|
|||
|
|
|
|||
|
|
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
|
|||
|
|
header('Content-Disposition: attachment;filename="clients_'.$date.'.xlsx"');
|
|||
|
|
header('Cache-Control: max-age=0');
|
|||
|
|
|
|||
|
|
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
|
|||
|
|
//echo file_get_contents("php://input");
|
|||
|
|
try{
|
|||
|
|
$writer->save('php://output');
|
|||
|
|
} catch (Exception $e) {
|
|||
|
|
echo $e->getMessage();
|
|||
|
|
// exception is raised and it'll be handled here
|
|||
|
|
// $e->getMessage() contains the error message
|
|||
|
|
}
|
|||
|
|
//}
|