Files
2025-08-02 16:30:27 +02:00

516 lines
23 KiB
PHP

<?php
namespace KupShop\OSSVatsBundle\Util;
use Doctrine\DBAL\Connection;
use KupShop\AdminBundle\Util\ActivityLog;
use KupShop\KupShopBundle\Context\CountryContext;
use KupShop\KupShopBundle\Context\LanguageContext;
use KupShop\KupShopBundle\Util\Contexts;
use KupShop\OSSVatsBundle\OSSVatsBundle;
use Query\Operator;
class VatsUtil
{
protected $countryCodes;
protected $languageIDs;
// wtf EU: https://publications.europa.eu/code/pdf/370000en.htm
// The two-letter ISO code should be used (ISO 3166 alpha-2), except for Greece, for which the abbreviation EL is recommended.
protected $euToIsoMap = [
'AT' => 'AT', 'BE' => 'BE', 'BG' => 'BG', 'CY' => 'CY', 'CZ' => 'CZ', 'DE' => 'DE',
'DK' => 'DK', 'EE' => 'EE', 'EL' => 'GR', 'ES' => 'ES', 'FI' => 'FI', 'FR' => 'FR',
'HR' => 'HR', 'HU' => 'HU', 'IE' => 'IE', 'IT' => 'IT', 'LT' => 'LT', 'LU' => 'LU',
'LV' => 'LV', 'MT' => 'MT', 'NL' => 'NL', 'PL' => 'PL', 'PT' => 'PT', 'RO' => 'RO',
'SE' => 'SE', 'SI' => 'SI', 'SK' => 'SK', 'UK' => 'UK', 'XI' => 'XI',
];
protected $ignoredCategories = ['MEDICAL_EQUIPMENT', 'PHARMACEUTICAL_PRODUCTS', 'SUPER_TEMPORARY', 'ZERO_RATE', 'ZERO_REDUCED_RATE', 'TEMPORARY', 'EXEMPTION_SUPERREDUCED'];
protected $isoToEuMap;
public function __construct()
{
$countryContext = Contexts::get(CountryContext::class);
$this->countryCodes = array_keys($countryContext->getAll());
$languageContext = Contexts::get(LanguageContext::class);
$this->languageIDs = array_keys($languageContext->getSupported());
$this->isoToEuMap = array_flip($this->euToIsoMap);
}
protected function getClient(): \SoapClient
{
return new \SoapClient('https://ec.europa.eu/taxation_customs/tedb/ws/VatRetrievalService.wsdl', [
'features' => SOAP_SINGLE_ELEMENT_ARRAYS,
]);
}
public function updateVatRates()
{
$euCodes = [];
foreach ($this->countryCodes as $isoCode) {
$tmpCode = $this->isoToEuMap[$isoCode] ?? null;
if (isset($tmpCode)) {
$euCodes[] = $tmpCode;
}
}
$client = $this->getClient();
try {
$result = $client->__soapCall('retrieveVatRates', [
'retrieveVatRatesReqMsg' => [
'memberStates' => $euCodes,
'situationOn' => (new \DateTime())->format('Y-m-d'),
],
]);
sqlGetConnection()->transactional(function () use ($result) {
$cnCodesByVatID = [];
$oldVats = sqlQueryBuilder()->select('id, vat, id_country, is_default, data')->from('vats')
->where('automanaged = 1')
->execute()->fetchAll();
foreach ($oldVats as $index => $row) {
$oldVats[$index]['data'] = !empty($row['data']) ? json_decode($row['data'], true) : [];
}
foreach ($result->vatRateResults as $vatRateResult) {
$this->processVatRateResult($vatRateResult, $oldVats, $cnCodesByVatID);
}
$vats0 = array_filter($oldVats, function ($v) { return $v['vat'] == 0; });
$vats0 = array_combine(array_column($vats0, 'id_country'), $vats0);
$countries = array_unique(array_column($oldVats, 'id_country'));
foreach ($countries as $id_country) {
if (!empty($vats0[$id_country])) {
$id = $vats0[$id_country]['id'];
} else {
// insert 0% vat for country
$newRow = ['descr' => '0%', 'vat' => 0, 'id_country' => $id_country, 'is_default' => 'N', 'automanaged' => true];
sqlQueryBuilder()->insert('vats')->directValues($newRow)->execute();
$newRow['id'] = sqlInsertId();
$newRow['data'] = ['level' => 'none', 'defaultLevel' => 'none'];
$oldVats[] = $newRow;
$id = $newRow['id'];
}
$cnCodesByVatID[$id]['00'] = '00';
}
foreach ($cnCodesByVatID as $vatID => $cnCodes) {
$customData = [];
foreach ($oldVats as $oldVatIndex => $oldVat) {
if ($vatID == $oldVat['id']) {
$customData = $oldVat['data'] ?? [];
break;
}
}
sqlQueryBuilder()->update('vats')
->directValues([
'data' => json_encode(array_merge($customData, ['cnCodes' => array_values($cnCodes)])),
])
->where(Operator::equals(['id' => $vatID]))->execute();
}
$vatsToRemove = [];
$cannotRemoveVats = [];
foreach ($oldVats as $oldVat) {
// $cnCodesByVatID contains all loaded vat rates - even those without cn codes
if (!isset($cnCodesByVatID[$oldVat['id']])) {
// TODO: temp solution
if ($pIds = sqlQueryBuilder()->select('id')->fromProducts()->where(Operator::equals(['vat' => $oldVat['id']]))->execute()->fetchFirstColumn()) {
$cannotRemoveVats['products'][$oldVat['id']] = $pIds;
} elseif ($dIDs = sqlQueryBuilder()->select('id')->from('delivery_type')->where(Operator::equals(['vat' => $oldVat['id']]))->execute()->fetchFirstColumn()) {
$cannotRemoveVats['delivery'][$oldVat['id']] = $dIDs;
} else {
$vatsToRemove[] = $oldVat['id'];
}
}
}
if (count($vatsToRemove)) {
sqlQueryBuilder()->delete('vats')
->where('automanaged = 1')
->andWhere(Operator::inStringArray($vatsToRemove, 'id'))
->execute();
}
if (count($cannotRemoveVats)) {
addActivityLog(ActivityLog::SEVERITY_WARNING, ActivityLog::TYPE_SYNC,
'Při aktualizaci OSS se nepodařilo odebrat některá DPH, protože jsou přiřazené u produktů nebo dopravy.',
$cannotRemoveVats, [OSSVatsBundle::LOG_TAG_OSS]);
}
});
} catch (\Exception $e) {
echo $e->getMessage();
echo PHP_EOL.PHP_EOL;
echo "REQUEST HEADERS:\n".$client->__getLastRequestHeaders()."\n";
throw $e;
}
}
protected function processVatRateResult($vatRateResult, &$oldVats, &$cnCodesByVatID)
{
$memberStateIso = ($this->euToIsoMap[$vatRateResult->memberState ?? null] ?? null);
if (!isset($vatRateResult->rate->value) || !in_array($memberStateIso, $this->countryCodes)) {
return; // vat rate is not set or unknown country code
}
// HACK:
// specialni podminka, kdy se snizene DPH vztahuje jen na urcity typ zbozi, ktere spada pod danou kategorii
// v datech nemame poradne jak poznat, ze se tam nachazi ta specialni podminka
// takze prozatim tenhle zaznam budeme skipovat - delalo to napr. problem na HF, kde kratasy mely
// DPH 7%, protoze byly v kategorii, ktera ma mit 7% v pripade, ze se jedna o nejaky lekarsky veci
if (($vatRateResult->type ?? false) === 'REDUCED'
&& in_array($vatRateResult->category->identifier ?? null, $this->ignoredCategories) !== false) {
return;
}
$isDefault = ($vatRateResult->type ?? false) === 'STANDARD' ? 'Y' : 'N';
$rate = toDecimal($vatRateResult->rate->value);
$level = null;
if (($vatRateResult->type ?? false) === 'REDUCED') {
if (($vatRateResult->rate->type ?? false) === 'EXEMPTED' || $rate->lowerThanOrEqual(\DecimalConstants::zero())) {
$level = 'none';
} else {
$level = 'low';
}
} elseif (($vatRateResult->type ?? false) === 'STANDARD') {
$level = 'high';
}
$vatRow = null;
foreach ($oldVats as $oldVatIndex => $oldVat) {
if ($oldVat['id_country'] !== $memberStateIso) {
continue; // country doesn't match
}
$oldVatRate = toDecimal($oldVat['vat']);
if ($rate->equals($oldVatRate)) {
$vatRow = $oldVat;
if ($oldVat['is_default'] !== $isDefault) {
// refresh is_default settings
sqlQueryBuilder()->update('vats')->directValues(['is_default' => $isDefault])
->where(Operator::equals(['id' => $oldVat['id']]))
->execute();
$oldVats[$oldVatIndex]['is_default'] = $isDefault;
}
// check level
if (isset($level)) {
$tmpCustomData = is_array($vatRow['data'] ?? false) ? $vatRow['data'] : [];
$tmpCustomData['defaultLevel'] = $level; // always update defaultLevel
if (!is_array($vatRow['data'] ?? false) || !array_key_exists('level', $vatRow['data'] ?? [])) {
// save level if it is not already present
$tmpCustomData['level'] = $level;
}
$vatRow['data'] = $tmpCustomData;
$oldVats[$oldVatIndex]['data'] = $tmpCustomData;
}
}
}
// add new vat row if not found
if (!isset($vatRow)) {
$newRow = [
'descr' => $rate->asString().'%',
'vat' => $rate->asFloat(),
'id_country' => $memberStateIso,
'is_default' => $isDefault,
'automanaged' => true,
];
sqlQueryBuilder()->insert('vats')->directValues($newRow)->execute();
$vatRow = [
'id' => sqlInsertId(),
'vat' => $rate->asFloat(),
'id_country' => $newRow['id_country'],
'is_default' => $newRow['is_default'],
'data' => ['level' => $level, 'defaultLevel' => $level],
];
$oldVats[] = $vatRow;
}
// $cnCodesByVatID must contain all loaded rates (including those without cn codes)
$cnCodesByVatID[$vatRow['id']] = $cnCodesByVatID[$vatRow['id']] ?? [];
foreach ($vatRateResult->cnCodes->code ?? [] as $cnCodeElement) {
if (isset($cnCodeElement->value)) {
$cnCodesByVatID[$vatRow['id']][$cnCodeElement->value] = $cnCodeElement->value;
}
}
}
public function refreshVatsCnsRelations(?array $refreshOnlyPassedCNKeys = null)
{
sqlGetConnection()->transactional(function () use ($refreshOnlyPassedCNKeys) {
// najdu všechny automanaged DPH
$vatsResults = sqlQueryBuilder()->select('id, id_country, is_default, data')
->from('vats')
->where('id_country IS NOT NULL AND automanaged = 1')
->execute();
$cnCodesByCountryAndVatID = [];
$standardVatByCountry = [];
// rozdělím DPH podle zemí a najdu standardní sazbu DPH
foreach ($vatsResults as $row) {
if ($row['is_default'] === 'Y') {
$standardVatByCountry[$row['id_country']] = $row['id'];
}
$customData = !empty($row['data']) ? @json_decode_strict($row['data'], true) : null;
if (empty($customData['cnCodes']) || !count($customData['cnCodes'])) {
continue;
}
$cnCodesByCountryAndVatID[$row['id_country']] = $cnCodesByCountryAndVatID[$row['id_country']] ?? [];
$cnCodesByCountryAndVatID[$row['id_country']][$row['id']] = $customData['cnCodes'];
}
$cnKeys = $refreshOnlyPassedCNKeys ?? [];
if (is_null($refreshOnlyPassedCNKeys)) {
// if $refreshOnlyPassedCNKeys is null => refresh all CN keys
// get default CN keys
$dbcfg = \Settings::getDefault();
if (isset($dbcfg['oss_vats']['default'])) {
$cnKeys[] = $dbcfg['oss_vats']['default'];
}
foreach ($this->languageIDs as $languageID) {
$dbcfg = \Settings::getFromCache($languageID);
if (isset($dbcfg['oss_vats']['default'])) {
$cnKeys[] = $dbcfg['oss_vats']['default'];
}
}
$productCNsResult = sqlQueryBuilder()->select('DISTINCT id_cn AS cnkey')
->from('products')
->where('id_cn IS NOT NULL')->execute();
foreach ($productCNsResult as $row) {
$cnKeys[] = $row['cnkey'];
}
// delete old relations
sqlQueryBuilder()->delete('vats_cns')
->andWhere(Operator::equals(['automanaged' => 1]))
->execute();
} else {
// delete only refreshed cnkeys relations
sqlQueryBuilder()->delete('vats_cns')
->where(Operator::inStringArray($refreshOnlyPassedCNKeys, 'id_cn'))
->andWhere(Operator::equals(['automanaged' => 1]))
->execute();
}
$results = sqlQuery(
'WITH RECURSIVE cte (required_cnkey, level_of_generalization, cnkey, cn, parent) AS (
SELECT cnkey AS required_cnkey, 0, cnkey, cn, parent
FROM kupshop_shared.oss_vats_categories
WHERE cnkey IN (:cnkeys)
GROUP BY cnkey
UNION ALL
SELECT required_cnkey, level_of_generalization + 1, ovc2.cnkey, ovc2.cn, ovc2.parent
FROM kupshop_shared.oss_vats_categories ovc2
INNER JOIN cte ON ovc2.cnkey = cte.parent
)
SELECT required_cnkey, level_of_generalization, cnkey, cn FROM cte WHERE cn IS NOT NULL
ORDER BY required_cnkey, level_of_generalization',
['cnkeys' => array_unique($cnKeys)],
['cnkeys' => Connection::PARAM_STR_ARRAY]
)->fetchAll();
$processedCountriesLists = [];
// $results contains multiple rows per single required_cnkey
// $results are ordered by required_cnkey and by level of generalization
// more specific categories (closer to required_cnkey) comes first and with lower level of generalization
foreach ($results as $cnCategory) {
$processedCountriesLists[$cnCategory['required_cnkey']] = $processedCountriesLists[$cnCategory['required_cnkey']] ?? [];
// shortcut for processed countries of current required_cnkey
$processedCountries = &$processedCountriesLists[$cnCategory['required_cnkey']];
if (count($processedCountries) == count($this->countryCodes)
&& !array_diff($processedCountries, $this->countryCodes)
) {
// current required_cnkey has been processed for all countries
// skip other (more general) cn categories of already processed required_cnkey
continue;
}
foreach ($cnCodesByCountryAndVatID as $countryID => $cnCodesByVatID) {
if (in_array($countryID, $processedCountries)) {
continue; // current required_cnkey is already processed for current country
}
foreach ($cnCodesByVatID as $vatID => $vatCNCodes) {
if (in_array($cnCategory['cn'], $vatCNCodes)) {
// hooray: there is a vat rate for this CN
sqlQuery('INSERT IGNORE INTO vats_cns (id_cn, id_vat) VALUES (:id_cn, :id_vat)', [
'id_cn' => $cnCategory['required_cnkey'],
'id_vat' => $vatID,
]);
$processedCountries[] = $countryID;
continue 2;
}
}
}
}
// TODO: add foreach for exception categories?
// check CN keys that were not found -> use STANDARD vat rate
foreach ($processedCountriesLists as $requiredCNKey => &$processedCountries) {
foreach ($this->countryCodes as $countryID) {
if (in_array($countryID, $processedCountries)) {
continue; // this CN key was already processed for current country
}
if (isset($standardVatByCountry[$countryID])) {
// use STANDARD vat rate for current country
sqlQuery(
'INSERT IGNORE INTO vats_cns (id_cn, id_vat) VALUES (:id_cn, :id_vat)',
[
'id_cn' => $requiredCNKey,
'id_vat' => $standardVatByCountry[$countryID],
]
);
$processedCountries[] = $countryID;
}
}
}
$currentVatsCnsExceptionsByCountry = [];
$currentVatsCnsExceptions = sqlQueryBuilder()->select('id_cn, id_country')
->from('vats_cns', 'vcn')
->innerJoin('vcn', 'vats', 'v', 'v.id = vcn.id_vat')
->where('id_cn < 0')
->execute();
foreach ($currentVatsCnsExceptions as $currentVatsCnsException) {
if (!isset($currentVatsCnsExceptionsByCountry[$currentVatsCnsException['id_country']])) {
$currentVatsCnsExceptionsByCountry[$currentVatsCnsException['id_country']] = [];
}
$currentVatsCnsExceptionsByCountry[$currentVatsCnsException['id_country']]["{$currentVatsCnsException['id_cn']}"] = $currentVatsCnsException;
}
$exceptionsProducts = sqlQueryBuilder()->select('DISTINCT id_cn')
->from('products')
->where('id_cn < 0')
->execute();
foreach ($exceptionsProducts as $exception) {
foreach ($this->countryCodes as $countryID) {
// check if exception already exists
if (isset($currentVatsCnsExceptionsByCountry[$countryID][$exception['id_cn']])) {
continue;
}
$vat = $this->findVatByCountryAndCn($cnCodesByCountryAndVatID, $standardVatByCountry, $countryID, $exception['id_cn']);
sqlQueryBuilder()->insert('vats_cns')
->directValues([
'id_cn' => $exception['id_cn'],
'id_vat' => $vat,
'automanaged' => 0,
])
->onDuplicateKeyUpdate(['id_cn'])
->execute();
}
}
});
}
private function findVatByCountryAndCn(array $data, array $standardVatByCountry, string $country, string $cn): int
{
foreach ($data[$country] as $vat => $cns) {
if (in_array($cn, $cns)) {
return $vat;
}
}
return $standardVatByCountry[$country] ?? 0;
}
public function getCNKey(string $cn): ?int
{
if (empty($cn)) {
return null;
}
$cnKey = sqlQueryBuilder()
->select('cnkey')
->from('kupshop_shared.oss_vats_categories')
->where(Operator::equals(['cnkey' => $cn, 'cn' => $cn], 'OR'))
->execute()->fetchOne();
if ($cnKey) {
return (int) $cnKey;
}
if (strlen($cn) == 8) {
$cnWithSpaces = substr($cn, 0, 4).' '.substr($cn, 4, 2).' '.substr($cn, 6, 2);
$cnKey = sqlQueryBuilder()
->select('cnkey')
->from('kupshop_shared.oss_vats_categories')
->where(Operator::equals(['cn' => $cnWithSpaces]))
->execute()->fetchOne();
if ($cnKey) {
return (int) $cnKey;
}
}
$cnkeyShort = ltrim(str_replace(' ', '', substr($cn, 0, 8)), '0');
$qb = sqlQueryBuilder()
->select('cnkey, cn')
->from('kupshop_shared.oss_vats_categories')
->where(Operator::like(['cnkey' => $cnkeyShort.'%']));
$cnKey = null;
foreach ($qb->execute() as $item) {
if (trim(str_replace(' ', '', $item['cn'])) === trim($cnkeyShort)) {
$cnKey = $item['cnkey'];
break;
} else {
$cnKey = $item['cnkey'];
}
}
if ($cnKey) {
return (int) $cnKey;
}
return null;
}
public function generateForNonEUCountries(): void
{
$countriesQb = sqlQueryBuilder()
->select('id')
->from('countries')
->andWhere(Operator::not(Operator::inStringArray($this->euToIsoMap, 'id')));
foreach ($countriesQb->execute() as $country) {
// find default vat for country
$vat = sqlQueryBuilder()->select('id')
->from('vats')
->andWhere(Operator::equals(['id_country' => $country['id'], 'is_default' => 'Y']))
->execute()->fetchOne();
if (!$vat) {
continue;
}
sqlQuery('INSERT IGNORE INTO vats_cns (id_cn, id_vat, automanaged)
SELECT vc.id_cn, :id_vat, 0 FROM vats_cns vc
JOIN vats v ON v.id=vc.id_vat
WHERE vc.automanaged = 1 AND NOT EXISTS (SELECT * FROM vats_cns vc2 JOIN vats v2 ON vc2.id_vat = v2.id WHERE vc.id_cn=vc2.id_cn AND v2.id_country=:id_country )
GROUP BY vc.id_cn',
[
'id_vat' => $vat,
'id_country' => $country['id'],
]
);
}
}
public function getEUOSSCountryCode($countryCode)
{
$swappedCountryISOs = array_flip($this->euToIsoMap);
return $swappedCountryISOs[$countryCode] ?? $countryCode;
}
}