145 lines
4.8 KiB
PHP
145 lines
4.8 KiB
PHP
<?php
|
|
|
|
namespace KupShop\OSSVatsBundle;
|
|
|
|
use KupShop\KupShopBundle\Context\CountryContext;
|
|
use KupShop\KupShopBundle\Context\VatContext;
|
|
use KupShop\KupShopBundle\Util\Contexts;
|
|
use PhpOffice\PhpSpreadsheet\Cell\DataType;
|
|
use PhpOffice\PhpSpreadsheet\Shared\Date;
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
|
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
|
|
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
|
|
use Query\Operator;
|
|
use Query\QueryBuilder;
|
|
|
|
class OssOrdersExcelExport extends \KupShop\OrderingBundle\OrdersExcelExport
|
|
{
|
|
protected $country;
|
|
|
|
public const UNDEF_COUNTRY = 'BEZ';
|
|
|
|
protected function exportSpreadsheet()
|
|
{
|
|
$spreadsheet = new Spreadsheet();
|
|
|
|
$countryContext = Contexts::get(CountryContext::class);
|
|
$worksheetIndex = 0;
|
|
$countries = $countryContext->getAll();
|
|
ksort($countries);
|
|
$countries[self::UNDEF_COUNTRY] = null;
|
|
|
|
foreach ($countries as $country => $_) {
|
|
$this->country = $country;
|
|
$this->vats = null;
|
|
$this->fields = null;
|
|
/** @var \Generator $data */
|
|
$data = $this->getData();
|
|
|
|
if (!$data->current()) {
|
|
continue;
|
|
}
|
|
if ($worksheetIndex == 0) {
|
|
$spreadsheet->removeSheetByIndex(0);
|
|
}
|
|
|
|
$worksheet = new Worksheet($spreadsheet, $country);
|
|
$sheet = $spreadsheet->addSheet($worksheet, $worksheetIndex++);
|
|
|
|
$this->exportWorksheet($sheet, $data);
|
|
}
|
|
|
|
$spreadsheet->setActiveSheetIndex(0);
|
|
|
|
return $spreadsheet;
|
|
}
|
|
|
|
protected function exportWorksheet(Worksheet $sheet, $data)
|
|
{
|
|
$rowIndex = 1;
|
|
|
|
// header
|
|
$columnIndex = 1;
|
|
foreach ($this->getFields() as $key => $value) {
|
|
$sheet->setCellValueByColumnAndRow($columnIndex, $rowIndex, $value['name'] ?? '');
|
|
if (!empty($value['width'])) {
|
|
$sheet->getColumnDimensionByColumn($columnIndex)->setWidth($value['width']);
|
|
}
|
|
$columnIndex++;
|
|
}
|
|
|
|
// data
|
|
$rowIndex++;
|
|
foreach ($data as $row) {
|
|
$columnIndex = 1;
|
|
foreach ($this->getFields() as $key => $value) {
|
|
if (($value['type'] ?? false) === 'datetime') {
|
|
$tmpValue = empty($row[$key]) ? '' : Date::PHPToExcel(new \DateTime($row[$key]));
|
|
$sheet->setCellValueByColumnAndRow($columnIndex, $rowIndex, $tmpValue);
|
|
} else {
|
|
$data_type = $value['type'] ?? DataType::TYPE_STRING;
|
|
$sheet->setCellValueExplicitByColumnAndRow(
|
|
$columnIndex,
|
|
$rowIndex,
|
|
$row[$key] ?? ($data_type == DataType::TYPE_NUMERIC ? null : ''),
|
|
$data_type
|
|
);
|
|
}
|
|
if (isset($value['format'])) {
|
|
$sheet->getStyleByColumnAndRow($columnIndex, $rowIndex)->getNumberFormat()
|
|
->setFormatCode($value['format']);
|
|
}
|
|
$columnIndex++;
|
|
}
|
|
$rowIndex++;
|
|
}
|
|
}
|
|
|
|
protected function getVats(): array
|
|
{
|
|
if (!isset($this->vats)) {
|
|
$vatContext = Contexts::get(VatContext::class);
|
|
$vatsSQL = sqlQueryBuilder()->select('vat')
|
|
->from('vats')
|
|
->where(
|
|
Operator::equalsNullable(['id_country' => ($vatContext->isCountryOssActive($this->country) ? $this->country : null)])
|
|
)
|
|
->orderBy('vat', 'DESC')->execute();
|
|
|
|
$orderVats = sqlQueryBuilder()->select('oi.tax as vat')
|
|
->from('orders', 'o')
|
|
->leftJoin('o', 'order_items', 'oi', 'o.id = oi.id_order')
|
|
->andWhere(Operator::equalsNullable(['o.delivery_country' => $this->country]))
|
|
->andWhere(Operator::inStringArray($this->orderIDs, 'o.id'))
|
|
->andWhere('oi.tax IS NOT NULL')
|
|
->orderBy('vat', 'DESC')
|
|
->groupBy('oi.tax')->execute();
|
|
|
|
$this->vats = sqlFetchAll($vatsSQL, 'vat') + sqlFetchAll($orderVats, 'vat');
|
|
}
|
|
|
|
return $this->vats;
|
|
}
|
|
|
|
protected function prepareQuery(): QueryBuilder
|
|
{
|
|
$qb = parent::prepareQuery();
|
|
$qb->andWhere(Operator::equals(['o.delivery_country' => ($this->country != self::UNDEF_COUNTRY) ? $this->country : '']));
|
|
|
|
return $qb;
|
|
}
|
|
|
|
public function export()
|
|
{
|
|
$spreadsheet = $this->exportSpreadsheet();
|
|
|
|
$filename = 'objednavky_'.date('Y-m-d_H-i');
|
|
header("Content-Disposition: attachment;filename=\"{$filename}.xls\"");
|
|
|
|
$objWriter = new Xlsx($spreadsheet);
|
|
$objWriter->save('php://output');
|
|
|
|
return;
|
|
}
|
|
}
|