901 lines
36 KiB
PHP
901 lines
36 KiB
PHP
<?php
|
||
|
||
require_once($_SERVER['DOCUMENT_ROOT'] . "/config.php");
|
||
require_once($_SERVER['DOCUMENT_ROOT'] . '/vendor/autoload.php');
|
||
|
||
if ($_SESSION['id'] == 5238) {
|
||
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: маскируем fio/phone владельцев-клиентов в Excel-выгрузке объектов.
|
||
$hideContactsExcel = function_exists('should_hide_contacts_for_current_user')
|
||
&& should_hide_contacts_for_current_user();
|
||
|
||
$post = clearInputData($_POST);
|
||
$section = $post['section'];
|
||
$db_sphinx = new MysqlPdo(hstsph2, null, null, null, true, '9306');
|
||
//$columns = $post['columns'];
|
||
$columns = json_decode($_POST['columns'], true);
|
||
|
||
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
||
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
|
||
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
|
||
$spreadsheet = new Spreadsheet();
|
||
$sheet = $spreadsheet->getActiveSheet();
|
||
|
||
if ($section == 'my_objects')
|
||
$sheet->setTitle('Мои объекты');
|
||
else if ($section == 'agency_objects')
|
||
$sheet->setTitle('Объекты в рекламе');
|
||
|
||
$arrColExccel = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ'];
|
||
|
||
$rowStart = 1;
|
||
|
||
$j = 0;
|
||
|
||
$arrRows = [];
|
||
$arrSearch = [];
|
||
|
||
foreach($columns as $col) {
|
||
$header = [];
|
||
|
||
if ($col['visible'] && (isset($col['field']) && $col['field'] != 'edit' && $col['field'] != 'archive' && $col['field'] != 'links')) {
|
||
$arrSearch[$col['field']] = $col;
|
||
$header['title'] = $col['title'];
|
||
if ($col['field'] == 'fio') {
|
||
$header['width'] = 35;
|
||
} else {
|
||
$header['width'] = 20;
|
||
}
|
||
$arrayHeder[$arrColExccel[$j]] = $header;
|
||
$arrRows[$arrColExccel[$j]] = $col['field'];
|
||
$j++;
|
||
}
|
||
else {
|
||
$should_check_id = isset($col['id']);
|
||
if ($col['visible'] && $should_check_id) {
|
||
if (($col['id'] != 'edit' && $col['id'] != 'archive' && $col['id'] != 'links')) {
|
||
$arrSearch[$col['id']] = $col;
|
||
$header['title'] = $col['text'];
|
||
if ($col['id'] == 'fio') {
|
||
$header['width'] = 35;
|
||
} else {
|
||
$header['width'] = 20;
|
||
}
|
||
$arrayHeder[$arrColExccel[$j]] = $header;
|
||
$arrRows[$arrColExccel[$j]] = $col['id'];
|
||
$j++;
|
||
}
|
||
}
|
||
}
|
||
}
|
||
$sql = '';
|
||
$objects = [];
|
||
if ($section == 'my_objects' && isset($_SESSION['my_objects_export'])) {
|
||
$sql = $_SESSION['my_objects_export'];
|
||
} else if ($section == 'agency_objects' && isset($_SESSION['agency_objects_export'])) {
|
||
$sql = $_SESSION['agency_objects_export'];
|
||
}
|
||
|
||
$agency_id = User::getUserAgencyID();
|
||
|
||
session_write_close();
|
||
|
||
//echo $sql;
|
||
$starttime = microtime(true);
|
||
|
||
if(!empty($sql)){
|
||
|
||
//echo $sql."\n";
|
||
$objSphs = array();
|
||
$rez = $db_sphinx->query($sql, true);
|
||
if ($db_sphinx->num_rows($rez)) {
|
||
$ids = array();
|
||
$userIds = array();
|
||
$managerIds = array();
|
||
|
||
while($objSph = $db_sphinx->fetch_assoc($rez)) {
|
||
$ids[] = $objSph['id'];
|
||
$userIds[] = $objSph['id_add_user'];
|
||
$objSphs[] = $objSph;
|
||
}
|
||
|
||
$sql2 = "SELECT objects.*,
|
||
rayon.rayon,
|
||
ads_objects.ads_id,
|
||
ads_objects.ads_source,
|
||
ads_objects.ads_agent,
|
||
ads_objects.ads_url,
|
||
DATE_FORMAT(objects.date_add,'%d.%m.%Y %H:%i') as date_add,
|
||
DATE_FORMAT(objects.date_really_add,'%d.%m.%Y %H:%i') as date_really_add,
|
||
lu.name as land_usage_name
|
||
FROM objects objects
|
||
LEFT JOIN rayon rayon on objects.rayon = rayon.id
|
||
LEFT JOIN land_usage_type lu on objects.land_usage_type_id = lu.id
|
||
LEFT JOIN ads_objects ON ads_objects.obj_id = objects.id
|
||
WHERE objects.id in (" . implode(", ", $ids) .") order by objects.date_add desc";
|
||
// echo $sql2;
|
||
|
||
//die();
|
||
|
||
|
||
//начитка доп. инфы списком
|
||
$rezAddInfoArray = [];
|
||
$sqlAddInfo = "SELECT object_id, apartments,
|
||
video_url,
|
||
balcony,
|
||
loggias,
|
||
separate_wcs,
|
||
combined_wcs,
|
||
passenger_lifts,
|
||
cargo_lifts,
|
||
window_view,
|
||
renovation,
|
||
ceiling_height,
|
||
vat_type_id,
|
||
house_category
|
||
FROM objects_additional_information
|
||
WHERE object_id in (" . implode(", ", $ids) .")";
|
||
$rezAddInfo = mysql_query($sqlAddInfo);
|
||
if (mysql_num_rows($rezAddInfo) > 0) {
|
||
while ($objAddInfo = mysql_fetch_assoc($rezAddInfo)) {
|
||
$rezAddInfoArray[$objAddInfo["object_id"]] = $objAddInfo;
|
||
}
|
||
}
|
||
|
||
//начитка фото списком
|
||
$rezPhotoArray = [];
|
||
$sqlPhotoInfo = "SELECT oop.sort_order, oop.object_id, p.photo
|
||
FROM objects_object_photo oop, object_photo p
|
||
WHERE p.id = oop.object_photo_id AND oop.object_id in (" . implode(", ", $ids) .")
|
||
ORDER BY oop.sort_order";
|
||
|
||
$rezPhotoInfo = mysql_query($sqlPhotoInfo);
|
||
|
||
if (mysql_num_rows($rezPhotoInfo) > 0) {
|
||
while ($objPhoto = mysql_fetch_assoc($rezPhotoInfo)) {
|
||
if (!isset($rezPhotoArray[$objPhoto["object_id"]])) {
|
||
$rezPhotoArray[$objPhoto["object_id"]] = [];
|
||
}
|
||
$rezPhotoArray[$objPhoto["object_id"]]['photo' . $objPhoto['sort_order']] = $objPhoto["photo"];
|
||
}
|
||
}
|
||
|
||
//начитываем цены
|
||
$objPricesArray = [];
|
||
$sqlPrices = "SELECT * FROM `object_prices`
|
||
WHERE `object_id` in (" . implode(", ", $ids) .") AND `agency_id`='$agency_id'";
|
||
|
||
if ($queryPrices = mysql_query($sqlPrices)) {
|
||
if (mysql_num_rows($queryPrices) > 0) {
|
||
while ($objPrice = mysql_fetch_assoc($queryPrices)) {
|
||
$objPricesArray[] = $objPrice;
|
||
}
|
||
}
|
||
}
|
||
|
||
//начитываем кол-во заявок для объектов
|
||
$objRequisitionsArray = [];
|
||
$sqlRequisitions = "SELECT count(id) as cnt, object_id FROM requisitions WHERE (type_id = 4 or type_id in (SELECT id FROM requisitions_type WHERE heir = 4 AND agency_id = {$agency_id})) AND cancel = 0 AND object_id in (" . implode(", ", $ids) .") GROUP BY object_id";
|
||
$qRequisitions = mysql_query($sqlRequisitions);
|
||
|
||
if(mysql_num_rows($qRequisitions) > 0){
|
||
while ($objRequisitions = mysql_fetch_assoc($qRequisitions)) {
|
||
$objRequisitionsArray[] = $objRequisitions;
|
||
}
|
||
}
|
||
|
||
$objMetroArray = [];
|
||
$sqlMetro = "SELECT GROUP_CONCAT(metro.metro SEPARATOR ', ') as metro_list, sp_metro.id_obj as id_obj FROM metro, sp_metro WHERE sp_metro.id_metro = metro.id AND sp_metro.id_obj in (" . implode(", ", $ids) .") GROUP BY sp_metro.id_obj";
|
||
$resultMetro = mysql_query($sqlMetro);
|
||
while ($mtr = mysql_fetch_assoc($resultMetro)) {
|
||
$objMetroArray[$mtr['id_obj']] = $mtr;
|
||
}
|
||
|
||
$presentationsCountArray = [];
|
||
$sqlPresentation = "SELECT COUNT(id) as cnt, id_object FROM sended_pdf WHERE id_object in (" . implode(", ", $ids) .") GROUP BY id_object";
|
||
$rezPresentation = mysql_query($sqlPresentation);
|
||
while ($prs = mysql_fetch_assoc($rezPresentation)) {
|
||
$presentationsCountArray[$prs['id_object']] = $prs['cnt'];
|
||
}
|
||
|
||
$meetCountArray = [];
|
||
$sqlMeet = "SELECT COUNT(id) as cnt, object_id FROM user_object_events WHERE object_id in (" . implode(", ", $ids) .") and `type` = 'show' and calendar_view = 1
|
||
and schedule_date is not null and DATE_FORMAT(schedule_date, '%Y-%m-%d %H:%i:%s') <= '".date("Y-m-d H:i:s")."' GROUP BY object_id";
|
||
$rezMeet = mysql_query($sqlMeet);
|
||
while ($mt = mysql_fetch_assoc($rezMeet)) {
|
||
$meetCountArray[$mt['object_id']] = $mt['cnt'];
|
||
}
|
||
|
||
//начитка тегов списком
|
||
$rezActivityArray = [];
|
||
|
||
$sqlAddInfo = "SELECT object_id, activity_id, (SELECT name FROM `activities` where objects_activities.activity_id = activities.id) as tag FROM objects_activities WHERE object_id in (" . implode(", ", $ids) .")";
|
||
$rezAddInfo = mysql_query($sqlAddInfo);
|
||
if (mysql_num_rows($rezAddInfo) > 0) {
|
||
while ($objActivity = mysql_fetch_assoc($rezAddInfo)) {
|
||
if (!isset($rezActivityArray[$objActivity["object_id"]])) {
|
||
$rezActivityArray[$objActivity["object_id"]] = [];
|
||
}
|
||
$rezActivityArray[$objActivity["object_id"]][] = $objActivity["tag"];
|
||
}
|
||
}
|
||
|
||
$usersArray = [];
|
||
if (count($userIds) > 0) {
|
||
$sqlUsers = "SELECT id, first_name, last_name, middle_name, id_manager, phone FROM users WHERE id in (" . implode(", ", $userIds) . ")";
|
||
$rezUsers = mysql_query($sqlUsers);
|
||
while ($uz = mysql_fetch_assoc($rezUsers)) {
|
||
if ($uz['id_manager'] && in_array($uz['id_manager'], $userIds) === false) {
|
||
$managerIds[] = $uz['id_manager'];
|
||
}
|
||
$usersArray[$uz['id']] = $uz;
|
||
}
|
||
}
|
||
|
||
if (count($managerIds) > 0) {
|
||
$sqlManags = "SELECT id, first_name, last_name, middle_name, id_manager, phone FROM users WHERE id in (" . implode(", ", $managerIds) .")";
|
||
$rezManags = mysql_query($sqlManags);
|
||
while ($mg = mysql_fetch_assoc($rezManags)) {
|
||
$usersArray[$mg['id']] = $mg;
|
||
}
|
||
}
|
||
|
||
|
||
$clientsArray = [];
|
||
$whereClPart = [];
|
||
|
||
$sql_cl_obj = "SELECT client_id FROM client_objects WHERE object_id in (".implode(',', $ids).")";
|
||
$q_cl_obj = mysql_query($sql_cl_obj);
|
||
|
||
|
||
while($r_cl_obj = mysql_fetch_assoc($q_cl_obj)){
|
||
$whereClPart[] = $r_cl_obj['client_id'];
|
||
}
|
||
$sqlClients = "SELECT `id`, `fio`, `email`, `objects`, `phone` FROM `clients` WHERE id in (" . implode(', ', $whereClPart) .")";
|
||
$rezClient = mysql_query($sqlClients);
|
||
if (mysql_num_rows($rezClient) > 0) {
|
||
while ($client = mysql_fetch_assoc($rezClient)) {
|
||
$clientsArray[] = $client;
|
||
}
|
||
}
|
||
|
||
//начитываем координаты списком
|
||
$objCoordinatesArray = [];
|
||
$sqlCoordinates = "SELECT address, object_id, latitude, longitude FROM object_location WHERE object_id in (" . implode(", ", $ids) .")";
|
||
$rezCoordinatesAll = mysql_query($sqlCoordinates);
|
||
if (mysql_num_rows($rezCoordinatesAll) > 0) {
|
||
while ($objCoords = mysql_fetch_assoc($rezCoordinatesAll)) {
|
||
$objCoordinatesArray[] = $objCoords;
|
||
}
|
||
}
|
||
|
||
$datesAndSumInPeriodArray = [];
|
||
|
||
$sqlDatesAndPriceInPeriod = "SELECT publish_start_date, publish_end_date, price, created_at, days_count, object_id FROM object_publish_statistic WHERE object_id in (" . implode(", ", $ids) .") AND on_moderation = 0";
|
||
$rezDatesAndSumInPeriod = mysql_query($sqlDatesAndPriceInPeriod);
|
||
if (mysql_num_rows($rezDatesAndSumInPeriod) > 0) {
|
||
while ($sumPer = mysql_fetch_assoc($rezDatesAndSumInPeriod)) {
|
||
if (!isset($datesAndSumInPeriodArray[$sumPer['object_id']])) {
|
||
$datesAndSumInPeriodArray[$sumPer['object_id']] = [];
|
||
}
|
||
|
||
$datesAndSumInPeriodArray[$sumPer['object_id']][] = $sumPer;
|
||
}
|
||
}
|
||
|
||
//Договоры
|
||
$sql_contracts = "SELECT *, (SELECT type FROM contract_types WHERE id=contracts_object.type) as type_name FROM `contracts_object` WHERE object_id in (".implode(',', $ids).")";
|
||
$q_contract = mysql_query($sql_contracts);
|
||
while($contract = mysql_fetch_assoc($q_contract)){
|
||
if(!isset($contracts[$contract['object_id']])){
|
||
$contracts[$contract['object_id']] = Array('types'=>'', 'numbers'=>'', 'dates'=>'');
|
||
}
|
||
if($contracts[$contract['object_id']]['types'] != '') $contracts[$contract['object_id']]['types'] .= '; ';
|
||
if($contracts[$contract['object_id']]['numbers'] != '') $contracts[$contract['object_id']]['numbers'] .= '; ';
|
||
if($contracts[$contract['object_id']]['dates'] != '') $contracts[$contract['object_id']]['dates'] .= '; ';
|
||
$contracts[$contract['object_id']]['types'] .= $contract['type_name'];
|
||
$contracts[$contract['object_id']]['numbers'] .= '№'.$contract['number'];
|
||
$contracts[$contract['object_id']]['dates'] .= date('d.m.Y',$contract['date_start']).' - '.date('d.m.Y',$contract['date_end']);
|
||
}
|
||
|
||
//Начитываем размещение рекламы
|
||
$objPromoAdsArray = array();
|
||
$sqlPromo = "SELECT * FROM ads_promo WHERE object_id in (" . implode(", ", $ids) .")";
|
||
$qPromo = mysql_query($sqlPromo);
|
||
while($rPromo = mysql_fetch_assoc($qPromo)){
|
||
$objPromoAdsArray[$rPromo['object_id']] = $rPromo;
|
||
}
|
||
|
||
}
|
||
//echo $sql2."\n";
|
||
$rez2 = mysql_query($sql2);
|
||
|
||
//var_dump($rez2);
|
||
while ($obj = mysql_fetch_assoc($rez2)) {
|
||
|
||
if (isset($rezAddInfoArray[$obj["id"]])) {
|
||
$objAddInfo = $rezAddInfoArray[$obj["id"]];
|
||
$obj['apartments'] = $objAddInfo['apartments'];
|
||
$obj['video_url'] = $objAddInfo['video_url'];
|
||
$obj['balcony'] = $objAddInfo['balcony'];
|
||
$obj['loggias'] = $objAddInfo['loggias'];
|
||
$obj['separate_wcs'] = $objAddInfo['separate_wcs'];
|
||
$obj['combined_wcs'] = $objAddInfo['combined_wcs'];
|
||
$obj['passenger_lifts'] = $objAddInfo['passenger_lifts'];
|
||
$obj['cargo_lifts'] = $objAddInfo['cargo_lifts'];
|
||
$obj['window_view'] = $objAddInfo['window_view'];
|
||
$obj['renovation'] = $objAddInfo['renovation'];
|
||
$obj['ceiling_height'] = $objAddInfo['ceiling_height'];
|
||
$obj['vat_type_id'] = $objAddInfo['vat_type_id'];
|
||
$obj['house_category'] = $objAddInfo['house_category'];
|
||
} else {
|
||
$obj['apartments'] = 0;
|
||
$obj['video_url'] = '';
|
||
$obj['balcony'] = null;
|
||
$obj['loggias'] = null;
|
||
$obj['separate_wcs'] = null;
|
||
$obj['combined_wcs'] = null;
|
||
$obj['passenger_lifts'] = null;
|
||
$obj['cargo_lifts'] = null;
|
||
$obj['window_view'] = 0;
|
||
$obj['renovation'] = null;
|
||
$obj['ceiling_height'] = null;
|
||
$obj['vat_type_id'] = 0;
|
||
$obj['house_category'] = 0;
|
||
}
|
||
$plan = "";
|
||
$sqlPlan = "SELECT * FROM objects_plan WHERE object_id = {$obj['id']}";
|
||
$rezPlan = mysql_query($sqlPlan);
|
||
if(mysql_num_rows($rezPlan) > 0){
|
||
$planInfo = mysql_fetch_assoc($rezPlan);
|
||
$plan = $planInfo['img_plan'];
|
||
}
|
||
$photo = "";
|
||
if (isset($rezPhotoArray[$obj["id"]])) {
|
||
foreach ($rezPhotoArray[$obj["id"]] as $key => $phot) {
|
||
if(count($rezPhotoArray[$obj["id"]]) > 1){
|
||
for ($n = 1; $n <= count($rezPhotoArray[$obj["id"]]); $n++) {
|
||
$photo = $photo . $rezPhotoArray[$obj["id"]]["photo".$n]. ';';
|
||
}
|
||
break;
|
||
}
|
||
if(count($rezPhotoArray[$obj["id"]]) == 1){
|
||
$photo = $phot;
|
||
}
|
||
}
|
||
}
|
||
|
||
$type = 'Объект';
|
||
|
||
switch ($obj['type']) {
|
||
case 1:
|
||
if ($obj['komnat'] == 1 && $obj['studio_flag']) {
|
||
$type = "Квартира-студия";
|
||
} else {
|
||
$type = "$obj[komnat]-комнатная квартира";
|
||
}
|
||
break;
|
||
case 2:
|
||
|
||
$type = "Комната в $obj[komnat]-комнатной квартире";
|
||
break;
|
||
case 3:
|
||
$type = "$obj[komnat]-комнатный дом";
|
||
break;
|
||
case 4:
|
||
switch ($obj['type_category']){
|
||
case 1:
|
||
$type = "Офис";
|
||
break;
|
||
case 2:
|
||
$type = "Склад";
|
||
break;
|
||
case 3:
|
||
$type = "Торговая площадь";
|
||
break;
|
||
case 4:
|
||
$type = "Производство";
|
||
break;
|
||
case 5:
|
||
$type = "Помещение свободного назначения";
|
||
break;
|
||
case 6:
|
||
$type = "Здание";
|
||
break;
|
||
case 7:
|
||
$type = "Гараж";
|
||
break;
|
||
case 8:
|
||
$type = "Готовый бизнес";
|
||
break;
|
||
case 9:
|
||
$type = "Коммерческая земля";
|
||
break;
|
||
default: $type = "Помещение";
|
||
|
||
}
|
||
break;
|
||
case 5:
|
||
$type = "Часть дома";
|
||
break;
|
||
case 6:
|
||
$type = "Комплекс";
|
||
break;
|
||
case 7:
|
||
$type = "Участок";
|
||
break;
|
||
default:
|
||
$type = "$obj[komnat]-комнатная квартира";
|
||
}
|
||
|
||
$metroRayon = '';
|
||
|
||
if ($obj['rayon']) {
|
||
$metroRayon .= $obj['rayon'] . ' район';
|
||
}
|
||
|
||
if (isset($objMetroArray[$obj['id']])) {
|
||
$metroRayon .= ' / ' . $objMetroArray[$obj['id']]['metro_list'];
|
||
}
|
||
|
||
if (isset($objCoordinatesArray)) {
|
||
foreach ($objCoordinatesArray as $objCoords) {
|
||
if ($objCoords['object_id'] == $obj['id']) {
|
||
$objCoordinates = $objCoords;
|
||
$obj['latitude'] = $objCoordinates['latitude'];
|
||
$obj['longitude'] = $objCoordinates['longitude'];
|
||
break;
|
||
}
|
||
}
|
||
} else {
|
||
$sql_coordinates = "SELECT address, latitude, longitude FROM object_location WHERE object_id=".$obj['id'];
|
||
$rezCoordinates = mysql_query($sql_coordinates);
|
||
if (mysql_num_rows($rezCoordinates) > 0) {
|
||
$objCoordinates = mysql_fetch_assoc($rezCoordinates);
|
||
$obj['latitude'] = $objCoordinates['latitude'];
|
||
$obj['longitude'] = $objCoordinates['longitude'];
|
||
}
|
||
}
|
||
|
||
if ($objCoordinates && isset($objCoordinates['address'])) {
|
||
$address = $objCoordinates['address'];
|
||
} else {
|
||
$address = $obj['adres'];
|
||
if ($obj['dom']) {
|
||
$address = $address . ", " . $obj['dom'];
|
||
}
|
||
if ($obj['korpus']) {
|
||
$address = $address . ", корпус " . $obj['korpus'];
|
||
}
|
||
if ($obj['litera']) {
|
||
$address = $address . ", литера " . $obj['litera'];
|
||
}
|
||
}
|
||
|
||
$etazh = '';
|
||
|
||
if ($obj['type'] == 3) {
|
||
if ($obj['etazh_iz'] * 1 != 0) {
|
||
$etazh = $obj['etazh_iz'] . " этажей";
|
||
}
|
||
}
|
||
|
||
if ($obj['etazh'] * 1 != 0) {
|
||
$etazh = $obj['etazh'] . ' этаж';
|
||
if ((int)$obj['etazh'] == 1 && !empty($obj['etaz_first_type'])) {
|
||
$etazh = $etaz_first_types[$obj['etaz_first_type']];
|
||
|
||
}
|
||
if ($obj['etazh_iz'] * 1 != 0) {
|
||
$etazh .= " из " . $obj['etazh_iz'];
|
||
}
|
||
}
|
||
|
||
list($dateAdd, $timeAdd) = explode(" ", $obj['date_add']);
|
||
list($d, $m, $y) = explode(".", $dateAdd);
|
||
|
||
$date_add = $d . " " . getRusMonth($m) . " " . $y;
|
||
$updateDate = $date_add . " " . $timeAdd;
|
||
|
||
list($dateAdd, $timeAdd) = explode(" ", $obj['date_really_add']);
|
||
list($d, $m, $y) = explode(".", $dateAdd);
|
||
|
||
$date_add = $d . " " . getRusMonth($m) . " " . $y;
|
||
$createDate = $date_add . " " . $timeAdd;
|
||
|
||
$agentData = [];
|
||
|
||
if (isset($usersArray[$obj['id_add_user']])) {
|
||
$agentData['fio'] = trim($usersArray[$obj['id_add_user']]['last_name'] . ' ' . $usersArray[$obj['id_add_user']]['first_name'] . ' ' . $usersArray[$obj['id_add_user']]['middle_name']);
|
||
$agentData['phone'] = $usersArray[$obj['id_add_user']]['phone'];
|
||
}
|
||
$managerData = [];
|
||
|
||
$managerId = $usersArray[$obj['id_add_user']]['id_manager'];
|
||
|
||
if (isset($agentData['id_manager']) && isset($managerId)) {
|
||
$managerData['fio'] = trim($usersArray[$managerId]['last_name'] . ' ' . $usersArray[$managerId]['first_name'] . ' ' . $usersArray[$managerId]['middle_name']);
|
||
$managerData['phone'] = $usersArray[$managerId]['phone'];
|
||
} else {
|
||
$managerData['fio'] = $agentData['fio'];
|
||
$managerData['phone'] = $agentData['phone'];
|
||
}
|
||
|
||
if ($objPricesArray) {
|
||
foreach ($objPricesArray as $objPrice) {
|
||
if ($objPrice["object_id"] == $obj["id"]) {
|
||
if (!is_null($objPrice['prep_price'])) {
|
||
$obj['prep_price'] = $objPrice['prep_price'];
|
||
}
|
||
|
||
if (!is_null($objPrice['start_price'])) {
|
||
$obj['start_price'] = $objPrice['start_price'];
|
||
}
|
||
|
||
if (!is_null($objPrice['fact_price'])) {
|
||
$obj['fact_price'] = $objPrice['fact_price'];
|
||
}
|
||
break;
|
||
}
|
||
}
|
||
}
|
||
$clients = '[';
|
||
|
||
$clCount = 0;
|
||
foreach ($clientsArray as $cl) {
|
||
if (strpos($cl['objects'], $obj['id']) !== false) {
|
||
if ($clCount > 0) {
|
||
$clients .= ',';
|
||
}
|
||
|
||
$fioExp = $hideContactsExcel ? mask_fio_if_contains_phone($cl['fio']) : $cl['fio'];
|
||
$phoneExp = $hideContactsExcel ? mask_client_phone($cl['phone']) : $cl['phone'];
|
||
$clients = $clients . '{"fio": "' . htmlspecialchars($fioExp) . '", "id" : "' . $cl['id'] . '", "phone" : "' . $phoneExp . '"}';
|
||
$clCount++;
|
||
}
|
||
}
|
||
|
||
$clients .= ']';
|
||
|
||
$requestsCount = 0;
|
||
|
||
if (isset($objRequisitionsArray)) {
|
||
foreach ($objRequisitionsArray as $objRequisition) {
|
||
if ($objRequisition["object_id"] == $obj["id"]) {
|
||
$requestsCount = $objRequisition["cnt"];
|
||
break;
|
||
}
|
||
}
|
||
}
|
||
|
||
$presentationsCount = 0;
|
||
|
||
if (isset($presentationsCountArray[$obj['id']])) {
|
||
$presentationsCount = $presentationsCountArray[$obj['id']];
|
||
}
|
||
|
||
$meetCount = 0;
|
||
|
||
if (isset($meetCountArray[$obj['id']])) {
|
||
$meetCount = $meetCountArray[$obj['id']];
|
||
}
|
||
|
||
$minDate = '';
|
||
$maxDate = '';
|
||
$sumPrice = 0;
|
||
$periods = [];
|
||
$periodsCount = 0;
|
||
|
||
if (array_key_exists($obj['id'], $datesAndSumInPeriodArray) === true) {
|
||
foreach ($datesAndSumInPeriodArray[$obj['id']] as $statRec) {
|
||
if (!$minDate) {
|
||
$minDate = $statRec['publish_start_date'];
|
||
} else {
|
||
if (strtotime($minDate) > strtotime($statRec['publish_start_date'])) {
|
||
$minDate = $statRec['publish_start_date'];
|
||
}
|
||
}
|
||
if ($statRec['days_count'] != 5000) {
|
||
if (!$maxDate) {
|
||
$maxDate = $statRec['publish_end_date'];
|
||
} else {
|
||
if (strtotime($maxDate) < strtotime($statRec['publish_end_date'])) {
|
||
$maxDate = $statRec['publish_end_date'];
|
||
}
|
||
}
|
||
}
|
||
|
||
$sumPrice = $sumPrice + $statRec['price'];
|
||
|
||
if ($periodsCount == 0) {
|
||
$p['publish_start_date'] = $statRec['publish_start_date'];
|
||
$p['publish_end_date'] = $statRec['days_count'] != 5000 ? $statRec['publish_end_date'] : '-';
|
||
$periods[] = $p;
|
||
$periodsCount++;
|
||
} else {
|
||
$found = false;
|
||
foreach ($periods as $key => $period) {
|
||
if ($period['publish_start_date'] === $statRec['publish_start_date'] && $period['publish_end_date'] === $statRec['publish_end_date']) {
|
||
//тот же период, ничего не делаем
|
||
$found = true;
|
||
}
|
||
}
|
||
if (!$found) {
|
||
$p['publish_start_date'] = $statRec['publish_start_date'];
|
||
$p['publish_end_date'] = $statRec['days_count'] != 5000 ? $statRec['publish_end_date'] : '-';
|
||
$periods[] = $p;
|
||
$periodsCount++;
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
$days = [];
|
||
|
||
foreach ($periods as $key => $period) {
|
||
$startStr = date("Y-m-d", strtotime($period['publish_start_date']));
|
||
$endStr = date("Y-m-d", strtotime($period['publish_end_date']));
|
||
|
||
$startAsDate = strtotime(date("Y-m-d", strtotime($period['publish_start_date'])));
|
||
$endAsDate = strtotime(date("Y-m-d", strtotime($period['publish_end_date'])));
|
||
|
||
$foundStart = false;
|
||
$foundEnd = false;
|
||
foreach ($days as $dayKey => $day) {
|
||
if ($day === $startStr) {
|
||
$foundStart = true;
|
||
}
|
||
if ($day === $endStr) {
|
||
$foundEnd = true;
|
||
}
|
||
}
|
||
if (!$foundStart) {
|
||
$days[] = $startStr;
|
||
}
|
||
if (!$foundEnd) {
|
||
$days[] = $endStr;
|
||
}
|
||
|
||
while (true) {
|
||
if ($startStr === $endStr) {
|
||
break;
|
||
}
|
||
|
||
if (strtotime($startStr) > strtotime($endStr)) {
|
||
break;
|
||
}
|
||
|
||
$startStr = date('Y-m-d', strtotime($startStr. ' + 1 days'));
|
||
|
||
$found = false;
|
||
foreach ($days as $dayKey => $day) {
|
||
if ($day === $startStr) {
|
||
$found = true;
|
||
}
|
||
}
|
||
if (!$found) {
|
||
$days[] = $startStr;
|
||
}
|
||
}
|
||
}
|
||
|
||
$packs = '';
|
||
$promo = [];
|
||
if(isset($objPromoAdsArray[$obj['id']])){
|
||
$promo = $objPromoAdsArray[$obj['id']];
|
||
}
|
||
if($obj['add_to_avito_feed'] > 0){
|
||
$packs .= 'Авито';
|
||
if(intval($promo['avito_highlight'])){
|
||
$packs .= " (Выделение объявления)";
|
||
} else if (intval($promo['avito_xl'])){
|
||
$packs .= " (XL-объявление)";
|
||
} else if (intval($promo['avito_x2_1'])){
|
||
$packs .= " (x2 просмотров на 1 день)";
|
||
} else if ((intval($promo['avito_x2_7']))){
|
||
$packs .= " (x2 просмотров на 7 дней)";
|
||
} else if ((intval($promo['avito_x5_1']))){
|
||
$packs .= " (x5 просмотров на 1 день)";
|
||
} else if ((intval($promo['avito_x5_7']))){
|
||
$packs .= " (x5 просмотров на 7 дней)";
|
||
} else if ((intval($promo['avito_x10_1']))){
|
||
$packs .= " (x10 просмотров на 1 день)";
|
||
} else if ((intval($promo['avito_x10_7']))){
|
||
$packs .= " (x10 просмотров на 7 дней)";
|
||
}
|
||
}
|
||
if($obj['add_to_cian_feed'] > 0){
|
||
if($packs != '') $packs .= ', ';
|
||
$packs .= 'Циан';
|
||
$pr = '';
|
||
if(intval($promo['cian_standard'])) {
|
||
if($pr == '') $pr .= "(";
|
||
$pr .= "Стандарт";
|
||
} else if(intval($promo['cian_premium'])) {
|
||
if($pr == '') $pr .= "(";
|
||
$pr .= "Премиум-объявление";
|
||
} else if(intval($promo['cian_top3'])) {
|
||
if($pr == '') $pr .= "(";
|
||
$pr .= "Топ";
|
||
} else if(intval($promo['cian_ignore_pkg'])) {
|
||
if($pr == '') $pr .= "(";
|
||
$pr .= "Не использовать пакет размещений";
|
||
}
|
||
if($promo['cian_bet'] > 0){
|
||
if($pr == '') $pr .= "(";
|
||
$pr .= " Ставка аукциона ".$promo['cian_bet'];
|
||
}
|
||
if($pr != ''){
|
||
$pr .= ")";
|
||
$packs .= " ".$pr;
|
||
}
|
||
|
||
}
|
||
if($obj['add_to_bn_feed'] > 0){
|
||
if($packs != '') $packs .= ', ';
|
||
$packs .= 'Яндекс';
|
||
if(intval($promo['yandex_premium'])){
|
||
$packs .= " (Премиум)";
|
||
} else if (intval($promo['yandex_raise'])){
|
||
$packs .= " (Поднятие)";
|
||
} else if (intval($promo['yandex_promotion'])){
|
||
$packs .= " (Продвижение)";
|
||
}
|
||
|
||
}
|
||
if($obj['add_to_domclick_feed'] > 0){
|
||
if($packs != '') $packs .= ', ';
|
||
$packs .= 'Домклик';
|
||
if(intval($promo['domclick_express'])){
|
||
$packs .= " (Экспресс)";
|
||
} else if (intval($promo['domclick_premium'])){
|
||
$packs .= " (Премиум)";
|
||
} else if (intval($promo['domclick_top'])){
|
||
$packs .= " (Топ)";
|
||
}
|
||
|
||
}
|
||
if($obj['add_to_yandex_feed'] > 0){
|
||
if($packs != '') $packs .= ', ';
|
||
$packs .= 'Бесплатные';
|
||
}
|
||
if($obj['add_to_emls_feed'] > 0){
|
||
if($packs != '') $packs .= ', ';
|
||
$packs .= 'Jcat';
|
||
}
|
||
|
||
$objects[] = [
|
||
'id' => $obj['id'],
|
||
'plan' => $plan,
|
||
'photo' => $photo,
|
||
'type' => $obj['operation_type'] == 1 ? "Продажа" : "Аренда",
|
||
'objType' => $type,
|
||
'metro' => $metroRayon,
|
||
'address' => $address,
|
||
'allArea' => $obj['type'] == 7 ? $obj['land_area'] * 1 : $obj['ploshad'] * 1,
|
||
'liveArea' => $obj['ploshad_komn'],
|
||
'kitchenArea' => $obj['ploshad_k'] * 1,
|
||
'landArea' => $obj['land_area'],
|
||
'nazv' => $obj['nazv'],
|
||
'opis' => $obj['opis'],
|
||
'floor' => $etazh,
|
||
'price' => number_format($obj['stoim'], 0, ',', ' '),
|
||
'prep_price' => isset($obj['prep_price']) && $obj['prep_price'] != '-' ? number_format($obj['prep_price'], 0, ',', ' ') : "-",
|
||
'start_price' => isset($obj['start_price']) && $obj['start_price'] != '-' ? number_format($obj['start_price'], 0, ',', ' ') : "-",
|
||
'fact_price' => isset($obj['fact_price']) && $obj['fact_price'] != '-' ? number_format($obj['fact_price'], 0, ',', ' ') : "-",
|
||
'manager' => $managerData['fio'],
|
||
'phone' => $managerData['phone'],
|
||
'agent' => $agentData['fio'],
|
||
'client' => $clients,
|
||
'createDate' => $createDate,
|
||
'updateDate' => $updateDate,
|
||
'requests' => $requestsCount,
|
||
'presentations' => $presentationsCount,
|
||
'meets' => $meetCount,
|
||
'advPacks' => $packs,
|
||
'advStart' => $minDate ? date("d.m.Y", strtotime($minDate)) : '',
|
||
'advEnd' => $maxDate ? date("d.m.Y", strtotime($maxDate)) : '',
|
||
'daysCount' => count($days),
|
||
'adv_price' => number_format($sumPrice, 2, '.', ' '),
|
||
'id1' => $obj['id'],
|
||
'id2' => $obj['id'],
|
||
'contract_type' => isset($contracts[$obj['id']]['types']) ? $contracts[$obj['id']]['types'] : '',
|
||
'contract_number' => isset($contracts[$obj['id']]['types']) ? $contracts[$obj['id']]['numbers'] : '',
|
||
'contract_date' => isset($contracts[$obj['id']]['dates']) ? $contracts[$obj['id']]['dates'] : '',
|
||
'cadastral_number' => $obj['cadastral_number'],
|
||
'tags' => (isset($rezActivityArray[$obj['id']]) && !empty($rezActivityArray[$obj['id']])) ? implode(', ', $rezActivityArray[$obj['id']]) : ''
|
||
];
|
||
|
||
}
|
||
}
|
||
|
||
if (count($objects) > 0) {
|
||
foreach($arrayHeder as $h => $w) {
|
||
$sheet->setCellValue($h.$rowStart, $w['title']);
|
||
$sheet->getColumnDimension($h)->setWidth($w['width']);
|
||
$sheet->getStyle($h.$rowStart)->getFont()->applyFromArray( [ 'name' => 'Arial', 'bold' => TRUE, 'italic' => FALSE, 'strikethrough' => FALSE, 'color' => [ 'rgb' => '808080' ], ] );
|
||
$sheet->getStyle($h)->getAlignment()->setWrapText(true);
|
||
$sheet->getStyle($h.$rowStart)->getAlignment()->setWrapText(true);
|
||
}
|
||
|
||
$i = $rowStart+1;
|
||
|
||
foreach ($objects as $object) {
|
||
|
||
$sheet->setCellValue('A'.$i, $k);
|
||
$j = 0;
|
||
|
||
foreach ($arrSearch as $key => $value) {
|
||
$value_to_set = html_entity_decode($object[$key]);
|
||
|
||
if ($key === 'preview') {
|
||
$value_to_set = html_entity_decode($object['photo']);
|
||
} else if ($key === 'id1' || $key === 'id2') {
|
||
$value_to_set = html_entity_decode($object['id']);
|
||
} else if ($key === 'client') {
|
||
$client = json_decode(html_entity_decode($object['client']), true);
|
||
if (isset($client[0]['fio']))
|
||
$value_to_set = $client[0]['fio'];
|
||
else
|
||
$value_to_set = '';
|
||
} else if ($key === 'clientPhone') {
|
||
$client = json_decode(html_entity_decode($object['client']), true);
|
||
if (isset($client[0]['phone']))
|
||
$value_to_set = $client[0]['phone'];
|
||
else
|
||
$value_to_set = '';
|
||
}
|
||
|
||
$sheet->setCellValue($arrColExccel[$j].$i, $value_to_set);
|
||
$j++;
|
||
}
|
||
|
||
$i++;
|
||
|
||
}
|
||
|
||
$styleArray = [
|
||
'borders' => [
|
||
'allBorders' => [
|
||
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
|
||
'color' => [
|
||
'rgb' => '808080'
|
||
],
|
||
],
|
||
],
|
||
];
|
||
$sheet->getStyle('A' . ($rowStart) . ':' . $arrColExccel[$j-1] . ($rowStart + count($objects)))->applyFromArray($styleArray);
|
||
|
||
}
|
||
|
||
$writer = new Xlsx($spreadsheet);
|
||
if (!file_exists($_SERVER['DOCUMENT_ROOT'] . '/server/excel')) {
|
||
mkdir($_SERVER['DOCUMENT_ROOT'] . '/server/excel', 0777);
|
||
}
|
||
|
||
$name_file = null;
|
||
$name = null;
|
||
$date = date('d.m.Y_H-i-s');
|
||
if ($section == 'my_objects') {
|
||
$name_file = '/server/excel/my_objects_' . $date . '.xlsx';
|
||
$name = 'my_objects_'.$date.'.xlsx';
|
||
} else if ($section == 'agency_objects') {
|
||
$name_file = '/server/xls/agency_objects_' . $date . '.xlsx';
|
||
$name = 'agency_objects_'.$date.'.xlsx';
|
||
}
|
||
|
||
if ($name_file && $name) {
|
||
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
|
||
$writer->save($_SERVER['DOCUMENT_ROOT'] . $name_file);
|
||
echo json_encode([
|
||
'file' => $name_file,
|
||
'result' => 'ok',
|
||
'name' => $name,
|
||
]);
|
||
}
|