Парсер цен на ZennoPoster. Выгружаем красивые Excel таблицы из MySQL на PHPExcel

[qwadra]

Вступление

Я знаю, что многим пользователям ZP, время от времени нужно выгружать данные в таблицы. Желательно в красивые таблицы, с форматированием. Когда мне впервые понадобилось это, я воспользовался статьей Генерация красивых Excel-отчётов по шаблону и первое время был рад. Но все же это напоминало костыли, которые периодически подводили. Решение интересное, но не гибкое и не надежное.

Спойлер: с помощью PHPExcel мы можем получить такую таблицу, с несколькими вкладками и с кликабельными ценами (ссылки на карточки товаров)

Я вообще считаю, что делать что-то серьезное, не для себя, на БД в .xlsx или .csv это не то что моветон, это опасно! Вам нужно хотя бы по лайту освоить БД,. Лично у меня ZP не раз выдавал финты с табличными документами, после которых все данные обнулялись, как сейчас модно говорить.
Поэтому крайне рекомендую переводить свои важные проекты на БД в MySQL. Это надежно, просто, быстро, одни плюсы. Синтаксис очень приятен и прост мне, кстати, открыли глаза эти видео-уроки.
Работа с БД в данной статье примитивна, но поможет новичку освоится и не боятся ее, в дальнейшем вы упретесь в проблемы, решаемые статьей Заготовка для работы с БД MySQL в кубике C#.

Применение

Я работаю в инет-магазине и по потребности начальства я соорудил браузерный шаблон, который парсит не только обычные цены, но и «скрытые» цены (при добавлении в корзину, авторизации, вводе промокода). Так мне и пришлось разбираться, как это сделать красиво и отказоустойчиво.
Но в данном примере я подготовил простейший парсинг цен, хоть и на запросах. Для примера достаточно. Все шаблоны, практически 1 в 1 повторяют друг друга. Единственное, у магазина Плеер.ру я завысил цены на 7%, для наглядности отчета, т.к. ц них почти все цены в демпинге (нужно просто удалить кубик на JS что бы видеть реальные).

По легенде, я сотрудник М-Видео, которому нужно следить за ценами конкурентов, что бы никто не нарушал РРЦ или нарушал, но не более, чем мы. Мы должны быть «в рынке».
То есть красным выделяется минимальное (или несколько, если равны), но если минимальное у МВидео, то не выделяем, все ок.

Так же есть второй лист, где выводится техническая информация, а именно дата и время обновления цены позиции, которая дольше сего не обновлялась. Это позволяет судить об актуальности данных и о том, что твой чертов шаблон работает.

Как это работает?

Основная таблица в БД и шаблон у нас Mvideo, в БД это выглядит так

За один цикл шаблон перебирает все строки и парсит по очереди, начиная с самой «устаревшей» цены. Для этого нам и нужен столбец time, куда мы устанавливаем время в Unix-формате, при каждом обновлении цены. Это некий аналог работы со списком, когда мы берем первую строку с удалением и кладем ее в конец.

Сопоставление по таблицам производится по столбцу name, поэтому важно, чтобы во всех таблицах он был идентичен.

Будем считать, что по шаблонам ZP и MySql, все понятно. Не хотелось бы в сотый раз поднимать тему MySql, все давно уже есть на форуме, ютубе и т.д. Единственное, что бы поднять БД, я использовал программу Open Server (для олдфагов подойдет и Денвер). Ну и конечно, если у вас есть хостинг, можно настроить на работу не на локалке, а удаленно. Я использую для работы Hostiman.

Импортируем прикрепленный дамп konkurs.sql в PHPAdmin вашего локального или удаленного сервера. В случае с Open Server, вам просто надо залить файлы папку localhost в ее директории и в браузере вбить localhost/gen.php, отчет скачается. Если заливаете на хостинг, надо редактировать файл conn.php и в шаблонах кубики БД.

Данные для подключения к БД хранятся в файле conn.php

Спойлер: Файл подключения к БД

<?php

try { $pdo = new PDO('mysql:host=localhost;dbname=konkurs', 'root', 'root'); }
catch (PDOException $e) { die($e->getMessage()); }

Основной код в файле gen.php, именно его мы будем запускать в браузере, а в ответ получать нашу форматированную табличку.

Спойлер: Код генерации таблицы

<?php
ini_set('max_execution_time', 9999);
ini_set('date.timezone', 'Europe/Moscow');
/*
Подключение к базе данных
*/
require_once 'conn.php';

$sth = $pdo->prepare("SET NAMES 'cp1251'");
$sth->execute();

$sth = $pdo->prepare("SELECT mvideo.category, mvideo.brand, mvideo.name, mvideo.price, becompact.price, techport.price, pleer.price, mvideo.time, becompact.time, techport.time, pleer.time, mvideo.url, becompact.url, techport.url, pleer.url  FROM mvideo LEFT JOIN pleer ON mvideo.name=pleer.name LEFT JOIN techport ON mvideo.name=techport.name LEFT JOIN becompact ON mvideo.name=becompact.name ORDER BY category, brand");
$sth->execute();
$mvideo = $sth->fetchAll(PDO::FETCH_NUM);

require_once('PHPExcel.php');
require_once('PHPExcel/Writer/Excel5.php');

// Создаем объект класса PHPExcel
$xls = new PHPExcel();
// Устанавливаем индекс активного листа
$xls->setActiveSheetIndex(0);
// Получаем активный лист
$sheet = $xls->getActiveSheet();
// Подписываем лист
$sheet->setTitle('Отчет');
$abc = ['A', 'B', 'C', 'D', 'E', 'F', 'G'];

// Объединяем ячейки
$sheet->mergeCells('A1:C1');
$sheet->mergeCells('D1:G1');
// $sheet->mergeCells('F1:H1');

$sheet->setCellValue("A1", 'Отчет по демпингу от '.date("d.m.Y"));
$sheet->setCellValue("D1", 'Цены');

// выравниваем содержимое ячеек по горизонтали и вертикали
$sheet->getStyle("A1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$sheet->getStyle("A1")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$sheet->getStyle("D1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$sheet->getStyle("D1")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

// устанавливаем высоту 1 и 2 строк
$sheet->getRowDimension(1)->setRowHeight(30);
$sheet->getRowDimension(2)->setRowHeight(22);

// цвет фона
$sheet->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$sheet->getStyle('A1')->getFill()->getStartColor()->setRGB('131a4c');
$sheet->getStyle('D1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$sheet->getStyle('D1')->getFill()->getStartColor()->setRGB('131a4c');

// шрифты и размеры текста
$sheet->getStyle('A1')->getFont()->setSize('18');
$sheet->getStyle('A1')->getFont()->setBold(true);
$sheet->getStyle('A1')->getFont()->getColor()->applyFromArray(array('rgb' => 'FFFFFF'));
$sheet->getStyle('D1')->getFont()->setBold(true);
$sheet->getStyle('D1')->getFont()->setSize('18');
$sheet->getStyle('D1')->getFont()->getColor()->applyFromArray(array('rgb' => 'FFFFFF'));

$sheet->setCellValue("A2", 'Категория');
$sheet->setCellValue("B2", 'Бренд');
$sheet->setCellValue("C2", 'Модель');
$sheet->setCellValue("D2", 'Mvideo');
$sheet->setCellValue("E2", 'Beecompact');
$sheet->setCellValue("F2", 'TechPort');
$sheet->setCellValue("G2", 'Pleer');

// устанавливаем стили для заголовков
foreach ($abc as $letter)
{
    $sheet->getColumnDimension($letter)->setWidth(18);
    $sheet->getStyle($letter.'2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    $sheet->getStyle($letter.'2')->getFill()->getStartColor()->setRGB('4f5584');
    $sheet->getStyle($letter.'2')->getFont()->getColor()->applyFromArray(array('rgb' => 'FFFFFF'));
    $sheet->getStyle($letter.'2')->getFont()->setBold(true);
    $sheet->getStyle($letter.'2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $sheet->getStyle($letter.'2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
}

$row = 3; // номер строки, с которой начинаются товары
$dates = ['mvideo' => [], 'beecompact' => [], 'techport' => [], 'pleer' => []];
// перебираем каждый товар из БД и добавляем в ячейки
foreach ($mvideo as $k => $v)
{
    foreach ($abc as $n => $letter)
    {
        if ($n <= 2 && $k%2 == 1)
        {
            $sheet->getStyle($letter.$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $sheet->getStyle($letter.$row)->getFill()->getStartColor()->setRGB('e7e5e6');
        }
        $sheet->setCellValue($letter.$row, mb_convert_encoding($v[$n],"UTF-8" , "Windows-1251"));
        $sheet->getStyle($letter.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        if ($n > 2 && strlen($v[$n]) > 1) $sheet->getCell($letter.$row)->getHyperlink()->seturl($v[$n+8]);
    }
    $prices = [];
    $letter_prices = ['mvideo' => 'D', 'beecompact' => 'E', 'techport' => 'F', 'pleer' => 'G'];
    (int)$v[3] !== 0 ? $prices['mvideo'] = (int)$v[3] : '';
    (int)$v[4] !== 0 ? $prices['beecompact'] = (int)$v[4] : '';
    (int)$v[5] !== 0 ? $prices['techport'] = (int)$v[5] : '';
    (int)$v[6] !== 0 ? $prices['pleer'] = (int)$v[6] : '';

    $min = array_keys($prices, min($prices));
    foreach ($min as $k => $key)
    {
        if ($key == 'mvideo') continue;
        $sheet->getStyle($letter_prices[$key].$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getStyle($letter_prices[$key].$row)->getFill()->getStartColor()->setRGB('ea6666');
    }

    $sheet->getRowDimension($row)->setRowHeight(18); // высота строки
    $row++;
    (int)$v[7] !== 0 ? $dates['mvideo'][] = (int)$v[7] : '';
    (int)$v[8] !== 0 ? $dates['beecompact'][] = (int)$v[8] : '';
    (int)$v[9] !== 0 ? $dates['techport'][] = (int)$v[9] : '';
    (int)$v[10] !== 0 ? $dates['pleer'][] = (int)$v[10] : '';
}
$rowAutoFilter = $sheet->getHighestRow();
$row--;

// рисуем рамки
$sheet->getStyle("A2:A".$row)->applyFromArray(
    array(
        'borders' => array(
            'right' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array('rgb' => '000000'))))
);
$sheet->getStyle("B2:B".$row)->applyFromArray(
    array(
        'borders' => array(
            'right' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array('rgb' => '000000'))))
);
$sheet->getStyle("C2:C".$row)->applyFromArray(
    array(
        'borders' => array(
            'right' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array('rgb' => '000000'))))
);
$sheet->getStyle("A".$row.":G".$row)->applyFromArray(
    array(
        'borders' => array(
            'bottom' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array('rgb' => '000000'))))
);
$sheet->getStyle("G2:G".$row)->applyFromArray(
    array(
        'borders' => array(
            'right' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array('rgb' => '000000'))))
);


$sheet->setAutoFilter('A2:G'.$rowAutoFilter);
$sheet->freezePane('A3');

// создаем новый лист
$xls->createSheet(1);
// Устанавливаем индекс активного листа
$xls->setActiveSheetIndex(1);
// Получаем активный лист
$sheet2 = $xls->getActiveSheet();
// Подписываем лист
$sheet2->setTitle('Tech Info');

// Объединяем ячейки
$sheet2->mergeCells('A1:B1');

// цвет фона
$sheet2->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$sheet2->getStyle('A1')->getFill()->getStartColor()->setRGB('131a4c');
$sheet2->getColumnDimension('A')->setWidth(18);
$sheet2->getColumnDimension('B')->setWidth(22);
$sheet2->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


$dates_ = [
    ['name' => 'Mvideo', 'time' => min($dates['mvideo'])],
    ['name' => 'Beecompact', 'time' => min($dates['beecompact'])],
    ['name' => 'TechPort', 'time' => min($dates['techport'])],
    ['name' => 'Pleer', 'time' => min($dates['pleer'])]
];

usort ($dates_, 'time_sort');

function time_sort($x, $y) {
    if ($x['time'] < $y['time']) {
        return true;
    } else if ($x['time'] > $y['time']) {
        return false;
    } else {
        return 0;
    }
}

$sheet2->setCellValue("A1", 'Актуальность баз');
foreach ($dates_ as $i => $date)
{
    $sheet2->setCellValue("A".($i+2), $date['name']);
    $sheet2->setCellValue("B".($i+2), date("d.m.Y H:i:s", $date['time']));
}

$sheet2->getStyle('B2')->getFont()->setBold(true);
$sheet2->getStyle('B3')->getFont()->setBold(true);
$sheet2->getStyle('B4')->getFont()->setBold(true);
$sheet2->getStyle('B5')->getFont()->setBold(true);

// рамка
$sheet2->getStyle("A2:A5")->applyFromArray(
    array(
        'borders' => array(
            'right' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array('rgb' => '000000'))))
);
$sheet2->getStyle("B2:B5")->applyFromArray(
    array(
        'borders' => array(
            'right' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array('rgb' => '000000'))))
);
$sheet2->getStyle("A5:B5")->applyFromArray(
    array(
        'borders' => array(
            'bottom' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array('rgb' => '000000'))))
);

// шрифты и размеры текста
$sheet2->getStyle('A1')->getFont()->setBold(true);
$sheet2->getStyle('A1')->getFont()->getColor()->applyFromArray(array('rgb' => 'FFFFFF'));

// делаем активной первый лист
$xls->setActiveSheetIndex(0);

// Выводим HTTP-заголовки
header ( "Expires: Mon, 1 Apr 1974 05:00:00 GMT" );
header ( "Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT" );
header ( "Cache-Control: no-cache, must-revalidate" );
header ( "Pragma: no-cache" );
header ( "Content-type: application/vnd.ms-excel" );
header ( "Content-Disposition: attachment; filename=Отчёт ".date('d.m.Y H:i', time()).".xls" ); // название файла при скачивании

$objWriter = new PHPExcel_Writer_Excel5($xls);
$objWriter->save('php://output');

Остальные файлы трогать не надо, это и есть наша библиотека PHPExcel. Ее разработку завершили лет 10 назад, но лучше еще ничего не придумали. Какой-то чел русифицировал, довольно не плохо, документацию к библиотеке, файл в приложении. Штука не самая простая, скажу сразу, мне помогал пхпшник. Но я даже ошибку в его коде нашел. Цель – показать вам возможность коллаборации ZP, MySQL и PHP. А именно это 3 кита здорового автоматизатора (хотя, конечно, есть другие решения). Я рекомендую всем разобраться с базами данных и как их использовать с зенкой, это мастхэв, если проекты не для себя, по крайней мере. А вникать в PHP или нет – выбор за вами. В конце концов вам за копеечку поможет пхпшник, как и мне в начале. Но проект будет просто конфетка, за который не стыдно просить денег у начальства.

 

[Supergrok]

Бинарный файл при работе со списками частенько ловлю при синем экране, или отключении света, поэтому в проэкте храню копии списков для работы.
Сложнее с профилями, если в момент отключения было обращение к нему, то краш полюбому. А этих профилей сотни, восстанавливать тот еще гемор.
Поэтому, займусь я изучением баз данных. Спасибо за пинок

 

[radv]

Цель – показать вам возможность коллаборации ZP, MySQL и PHP.

Полезная возможность. Спасибо за инфу

 

[Koqpe]

Сложнее с профилями, если в момент отключения было обращение к нему, то краш полюбому. А этих профилей сотни, восстанавливать тот еще гемор.

Я на своем домашнем сервере пользуюсь программой Iperius Backup (источник рутрекер), бикапит БД, папки, файлы, есть возможность настроить несколько источников и мест для сохранения, все на автомате. Сон стал намного спокойнее и продолжительней .

 

[qwadra]

Залил дамп своей базы