mirror of
https://github.com/Karaka-Management/oms-Customs.git
synced 2026-01-12 01:48:41 +00:00
561 lines
20 KiB
PHP
Executable File
561 lines
20 KiB
PHP
Executable File
<?php
|
|
/**
|
|
* Jingga
|
|
*
|
|
* PHP Version 8.2
|
|
*
|
|
* @package Modules\Customs
|
|
* @copyright Dennis Eichhorn
|
|
* @license OMS License 2.0
|
|
* @version 1.0.0
|
|
* @link https://jingga.app
|
|
*/
|
|
declare(strict_types=1);
|
|
|
|
namespace Modules\Customs\Controller;
|
|
|
|
use phpOMS\Contract\RenderableInterface;
|
|
use phpOMS\DataStorage\Database\Query\Builder;
|
|
use phpOMS\Message\RequestAbstract;
|
|
use phpOMS\Message\ResponseAbstract;
|
|
use phpOMS\System\File\SearchUtils;
|
|
use phpOMS\Views\View;
|
|
|
|
/**
|
|
* Customs class.
|
|
*
|
|
* @package Modules\Customs
|
|
* @license OMS License 2.0
|
|
* @link https://jingga.app
|
|
* @since 1.0.0
|
|
* @codeCoverageIgnore
|
|
*/
|
|
final class BackendController extends Controller
|
|
{
|
|
/**
|
|
* Backend method to handle basic search request
|
|
*
|
|
* @param RequestAbstract $request Request
|
|
* @param ResponseAbstract $response Response
|
|
* @param array $data Generic data
|
|
*
|
|
* @return RenderableInterface
|
|
*
|
|
* @api
|
|
*
|
|
* @since 1.0.0
|
|
*/
|
|
public function viewSanctionView(RequestAbstract $request, ResponseAbstract $response, array $data = []) : RenderableInterface
|
|
{
|
|
$view = new View($this->app->l11nManager, $request, $response);
|
|
$view->setTemplate('/Modules/Customs/Theme/Backend/sanction-view');
|
|
|
|
return $view;
|
|
}
|
|
|
|
/**
|
|
* Backend method to handle basic search request
|
|
*
|
|
* @param RequestAbstract $request Request
|
|
* @param ResponseAbstract $response Response
|
|
* @param array $data Generic data
|
|
*
|
|
* @return RenderableInterface
|
|
*
|
|
* @api
|
|
*
|
|
* @since 1.0.0
|
|
*/
|
|
public function viewSanctionDashboard(RequestAbstract $request, ResponseAbstract $response, array $data = []) : RenderableInterface
|
|
{
|
|
$view = new View($this->app->l11nManager, $request, $response);
|
|
$view->setTemplate('/Modules/Customs/Theme/Backend/sanction-dashboard');
|
|
|
|
$view->data['sanctions'] = [];
|
|
|
|
if (!$request->hasData('name')) {
|
|
return $view;
|
|
}
|
|
|
|
$con = new \phpOMS\DataStorage\Database\Connection\SQLiteConnection([
|
|
'db' => 'sqlite',
|
|
'database' => __DIR__ . '/../Data/customs.sqlite',
|
|
]);
|
|
|
|
$con->connect();
|
|
|
|
$query = new Builder($con);
|
|
|
|
$nameString = \str_replace(['.', ';', ',', '-'], ' ', $request->getDataString('name') ?? '');
|
|
$names = \explode(' ', $nameString);
|
|
$fileSearchArray = $names;
|
|
$name = '';
|
|
|
|
// US SDN and CONS
|
|
foreach ($names as $idx => $n) {
|
|
if (empty($n)) {
|
|
continue;
|
|
}
|
|
|
|
$name .= ' AND (SDN_name LIKE :name' . $idx . ' OR alt_name LIKE :name' . $idx . ')';
|
|
$query->bind(['value' => '%' . $n . '%'], ':name'. $idx);
|
|
}
|
|
|
|
$name = \ltrim($name, ' AND');
|
|
|
|
$address = '';
|
|
if ($request->hasData('address')) {
|
|
$address = 'AND (Address LIKE :address OR Address LIKE "%-0-%")';
|
|
$query->bind(['value' => '%' . $request->getDataString('address') . '%'], ':address');
|
|
$fileSearchArray[] = $request->getDataString('address');
|
|
}
|
|
|
|
$city = '';
|
|
if ($request->hasData('city')) {
|
|
$city = 'AND (City_Province_PostalCode LIKE :city OR City_Province_PostalCode LIKE "%-0-%")';
|
|
$query->bind(['value' => '%' . $request->getDataString('city') . '%'], ':city');
|
|
$fileSearchArray[] = $request->getDataString('city');
|
|
}
|
|
|
|
$country = '';
|
|
if ($request->hasData('country')) {
|
|
$country = 'AND (Country LIKE :country OR Country LIKE "%-0-%")';
|
|
$query->bind(['value' => '%' . $request->getDataString('country') . '%'], ':country');
|
|
$fileSearchArray[] = $request->getDataString('country');
|
|
}
|
|
|
|
$sql = <<<SQL
|
|
SELECT sanction_us_sdn.Ent_num,
|
|
sanction_us_sdn.SDN_name, sanction_us_sdn_alt.alt_name,
|
|
sanction_us_sdn_add.Address, sanction_us_sdn_add.City_Province_PostalCode, sanction_us_sdn_add.Country,
|
|
sanction_us_sdn.Remarks,
|
|
'US_SDN' as sanction_db
|
|
FROM sanction_us_sdn
|
|
LEFT JOIN sanction_us_sdn_alt ON sanction_us_sdn.Ent_num = sanction_us_sdn_alt.Ent_num
|
|
LEFT JOIN sanction_us_sdn_add ON sanction_us_sdn.Ent_num = sanction_us_sdn_add.Ent_num
|
|
WHERE {$name}
|
|
{$address}
|
|
{$city}
|
|
{$country}
|
|
LIMIT 100;
|
|
SQL;
|
|
|
|
$view->data['sanctions'] = \array_merge($view->data['sanctions'], $query->raw($sql)->execute()?->fetchAll() ?? []);
|
|
|
|
$sql = <<<SQL
|
|
SELECT sanction_us_cons.Ent_num,
|
|
sanction_us_cons.SDN_name, sanction_us_cons_alt.alt_name,
|
|
sanction_us_cons_add.Address, sanction_us_cons_add.City_Province_PostalCode, sanction_us_cons_add.Country,
|
|
sanction_us_cons.Remarks,
|
|
'US_CONS' as sanction_db
|
|
FROM sanction_us_cons
|
|
LEFT JOIN sanction_us_cons_alt ON sanction_us_cons.Ent_num = sanction_us_cons_alt.Ent_num
|
|
LEFT JOIN sanction_us_cons_add ON sanction_us_cons.Ent_num = sanction_us_cons_add.Ent_num
|
|
WHERE {$name}
|
|
{$address}
|
|
{$city}
|
|
{$country}
|
|
LIMIT 100;
|
|
SQL;
|
|
|
|
$view->data['sanctions'] = \array_merge($view->data['sanctions'], $query->raw($sql)->execute()?->fetchAll() ?? []);
|
|
|
|
// EU Consolidated
|
|
$name = '';
|
|
|
|
foreach ($names as $idx => $n) {
|
|
if (empty($n)) {
|
|
continue;
|
|
}
|
|
|
|
$name .= ' AND (sanction_eu_cons.NameAlias_WholeName LIKE :name' . $idx . ' OR sanction_eu_cons.NameAlias_Title LIKE :name' . $idx . ')';
|
|
$query->bind(['value' => '%' . $n . '%'], ':name' . $idx);
|
|
}
|
|
|
|
$name = \ltrim($name, ' AND');
|
|
|
|
$address = '';
|
|
if ($request->hasData('address')) {
|
|
$address = 'AND (address_subquery.Address LIKE :address OR address_subquery.Address = "")';
|
|
$query->bind(['value' => '%' . $request->getDataString('address') . '%'], ':address');
|
|
}
|
|
|
|
$city = '';
|
|
if ($request->hasData('city')) {
|
|
$city = 'AND (address_subquery.City_Province_PostalCode LIKE :city OR address_subquery.City_Province_PostalCode = "")';
|
|
$query->bind(['value' => '%' . $request->getDataString('city') . '%'], ':city');
|
|
}
|
|
|
|
$country = '';
|
|
if ($request->hasData('country')) {
|
|
$country = 'AND (address_subquery.Country LIKE :country OR address_subquery.Country = "")';
|
|
$query->bind(['value' => '%' . $request->getDataString('country') . '%'], ':country');
|
|
}
|
|
|
|
$birthday = '';
|
|
if ($request->hasData('birthday')) {
|
|
$birthday = 'AND (DATE(sanction_eu_cons.BirthDate_BirthDate) = DATE(:birthday) OR sanction_eu_cons.BirthDate_BirthDate = "" OR DATE(address_subquery.BirthDate_BirthDate) = DATE(:birthday) OR address_subquery.BirthDate_BirthDate = "")';
|
|
$query->bind(['value' => $request->getDataString('birthday')], ':birthday');
|
|
}
|
|
|
|
$identno = '';
|
|
if ($request->hasData('identno')) {
|
|
$identno = 'AND (sanction_eu_cons.Identification_Number LIKE :identno OR sanction_eu_cons.Identification_Number = "" OR address_subquery.Identification_Number LIKE :identno OR address_subquery.Identification_Number = "")';
|
|
$query->bind(['value' => '%' . $request->getDataString('identno') . '%'], ':identno');
|
|
}
|
|
|
|
$sql = <<<SQL
|
|
SELECT
|
|
sanction_eu_cons.Entity_LogicalId as Ent_num,
|
|
sanction_eu_cons.NameAlias_WholeName AS SDN_name,
|
|
sanction_eu_cons.NameAlias_Title AS alt_name,
|
|
address_subquery.Address AS Address,
|
|
address_subquery.City_Province_PostalCode AS City_Province_PostalCode,
|
|
address_subquery.Country AS Country,
|
|
sanction_eu_cons.Entity_Remark AS Remarks,
|
|
'EU_CONS' AS sanction_db
|
|
FROM sanction_eu_cons
|
|
LEFT JOIN
|
|
(
|
|
SELECT
|
|
Entity_LogicalId,
|
|
Address_Street AS Address,
|
|
Address_City AS City_Province_PostalCode,
|
|
Address_CountryDescription AS Country
|
|
FROM
|
|
sanction_eu_cons
|
|
WHERE
|
|
Address_Street <> "" OR Address_City <> "" OR Address_CountryDescription <> ""
|
|
) AS address_subquery
|
|
ON
|
|
sanction_eu_cons.Entity_LogicalId = address_subquery.Entity_LogicalId
|
|
WHERE {$name}
|
|
{$address}
|
|
{$city}
|
|
{$country}
|
|
{$birthday}
|
|
{$identno}
|
|
GROUP BY sanction_eu_cons.Entity_LogicalId,
|
|
sanction_eu_cons.NameAlias_WholeName,
|
|
sanction_eu_cons.NameAlias_Title,
|
|
address_subquery.Address,
|
|
address_subquery.City_Province_PostalCode,
|
|
address_subquery.Country,
|
|
sanction_eu_cons.Entity_Remark,
|
|
'EU_CONS'
|
|
LIMIT 500;
|
|
SQL;
|
|
|
|
$view->data['sanctions'] = \array_merge($view->data['sanctions'], $query->raw($sql)->execute()?->fetchAll() ?? []);
|
|
|
|
$con->close();
|
|
|
|
// EU 881/2002
|
|
$positions = SearchUtils::findInFile(__DIR__ . '/../Data/Sanctions/EU/CELEX 32002R0881 EN TXT.html', $fileSearchArray);
|
|
|
|
$lex_881_2002 = [];
|
|
$hashResults = [];
|
|
foreach ($positions as $position) {
|
|
if ($position['distance'] > 500) {
|
|
continue;
|
|
}
|
|
|
|
$sanction = \trim(\strip_tags(
|
|
SearchUtils::getTextExtract(
|
|
__DIR__ . '/../Data/Sanctions/EU/CELEX 32002R0881 EN TXT.html',
|
|
$position['start'],
|
|
'<p', '</p>'
|
|
)
|
|
));
|
|
|
|
if (\in_array($hash = \sha1($sanction), $hashResults)) {
|
|
continue;
|
|
}
|
|
|
|
$hashResults[] = $hash;
|
|
$lex_881_2002[] = [
|
|
'sanction_db' => 'EU_881/2002',
|
|
'Ent_num' => 'EU_881/2002',
|
|
'parsed' => $sanction,
|
|
];
|
|
}
|
|
|
|
$view->data['sanctions'] = \array_merge($view->data['sanctions'], $lex_881_2002);
|
|
|
|
// EU 753/2011
|
|
$positions = SearchUtils::findInFile(__DIR__ . '/../Data/Sanctions/EU/CELEX 32011R0753 EN TXT.html', $fileSearchArray);
|
|
|
|
$lex_753_2011 = [];
|
|
$hashResults = [];
|
|
foreach ($positions as $position) {
|
|
if ($position['distance'] > 500) {
|
|
continue;
|
|
}
|
|
|
|
$sanction = \trim(\strip_tags(
|
|
SearchUtils::getTextExtract(
|
|
__DIR__ . '/../Data/Sanctions/EU/CELEX 32011R0753 EN TXT.html',
|
|
$position['start'],
|
|
'<p', '</p>'
|
|
)
|
|
));
|
|
|
|
if (\in_array($hash = \sha1($sanction), $hashResults)) {
|
|
continue;
|
|
}
|
|
|
|
$hashResults[] = $hash;
|
|
$lex_753_2011[] = [
|
|
'sanction_db' => 'EU_753/2011',
|
|
'Ent_num' => 'EU_753/2011',
|
|
'parsed' => $sanction,
|
|
];
|
|
}
|
|
|
|
$view->data['sanctions'] = \array_merge($view->data['sanctions'], $lex_753_2011);
|
|
|
|
// EU 2024/385
|
|
$positions = SearchUtils::findInFile(__DIR__ . '/../Data/Sanctions/EU/CELEX 32011R0753 EN TXT.html', $fileSearchArray);
|
|
|
|
$lex_2024_385 = [];
|
|
$hashResults = [];
|
|
foreach ($positions as $position) {
|
|
if ($position['distance'] > 500) {
|
|
continue;
|
|
}
|
|
|
|
$sanction = \trim(\strip_tags(
|
|
SearchUtils::getTextExtract(
|
|
__DIR__ . '/../Data/Sanctions/EU/CELEX 32011R0753 EN TXT.html',
|
|
$position['start'],
|
|
'<tr', '</tr>'
|
|
)
|
|
));
|
|
|
|
if (\in_array($hash = \sha1($sanction), $hashResults)) {
|
|
continue;
|
|
}
|
|
|
|
$hashResults[] = $hash;
|
|
$lex_2024_385[] = [
|
|
'sanction_db' => 'EU_2024/385',
|
|
'Ent_num' => 'EU_2024/385',
|
|
'parsed' => $sanction,
|
|
];
|
|
}
|
|
|
|
$view->data['sanctions'] = \array_merge($view->data['sanctions'], $lex_2024_385);
|
|
|
|
return $view;
|
|
}
|
|
|
|
/**
|
|
* Create HS code view
|
|
*
|
|
* @param RequestAbstract $request Request
|
|
* @param ResponseAbstract $response Response
|
|
* @param array $data Generic data
|
|
*
|
|
* @return RenderableInterface
|
|
*
|
|
* @api
|
|
*
|
|
* @since 1.0.0
|
|
*/
|
|
public function viewHSCodeView(RequestAbstract $request, ResponseAbstract $response, array $data = []) : RenderableInterface
|
|
{
|
|
$view = new View($this->app->l11nManager, $request, $response);
|
|
$view->setTemplate('/Modules/Customs/Theme/Backend/hscode-view');
|
|
|
|
$original = $request->getDataString('id') ?? '';
|
|
|
|
if (!$request->hasData('id')
|
|
|| \preg_match('/^[0-9 ]+$/', $original) !== 1
|
|
) {
|
|
return $view;
|
|
}
|
|
|
|
$con = new \phpOMS\DataStorage\Database\Connection\SQLiteConnection([
|
|
'db' => 'sqlite',
|
|
'database' => __DIR__ . '/../Data/customs.sqlite',
|
|
]);
|
|
|
|
$con->connect();
|
|
|
|
$length = \strlen($original);
|
|
|
|
// Also the footnotes of goods_code XX, XXXX, XXXXXX apply besides the actual code
|
|
$codes = [
|
|
$original,
|
|
\substr($original, 0, 2) . \str_repeat('0', $length - 2),
|
|
\substr($original, 0, 4) . \str_repeat('0', $length - 4),
|
|
\substr($original, 0, 6) . \str_repeat('0', $length - 6),
|
|
];
|
|
$codeString = '"' . \implode('","', $codes) . '"';
|
|
|
|
$taricString = '';
|
|
foreach ($codes as $code) {
|
|
$taricString .= ' OR taric_good.Goods_code LIKE "' . $code . '%"';
|
|
}
|
|
|
|
$taricString = \trim($taricString, 'OR ');
|
|
|
|
$sql = <<<SQL
|
|
SELECT *
|
|
FROM taric_good
|
|
WHERE ({$taricString})
|
|
AND taric_good.Language = "EN"
|
|
ORDER BY taric_good.Goods_code ASC;
|
|
SQL;
|
|
|
|
$query = new Builder($con);
|
|
|
|
$view->data['goods'] = $query->raw($sql)->execute()?->fetchAll() ?? [];
|
|
|
|
if (empty($view->data['goods'])) {
|
|
return $view;
|
|
}
|
|
|
|
$sql = <<<SQL
|
|
SELECT taric_measure_footnote.*,
|
|
taric_add_code.Description as Add_Description,
|
|
taric_footnote_description.Description as Footnote_Description,
|
|
taric_duties_export.Duty as Export_Duty,
|
|
taric_duties_import.Duty as Import_Duty
|
|
FROM taric_measure_footnote
|
|
LEFT JOIN taric_add_code ON taric_measure_footnote.Add_code = taric_add_code.Add_code
|
|
AND taric_add_code.Language = 'EN'
|
|
LEFT JOIN taric_footnote_description ON taric_measure_footnote.Footnote = taric_footnote_description.Footnote
|
|
AND taric_footnote_description.Language = 'EN'
|
|
LEFT JOIN taric_duties_export ON taric_measure_footnote.Goods_code = taric_duties_export.Goods_code
|
|
AND taric_duties_export.Dest_code = taric_measure_footnote.Origin_code
|
|
AND taric_duties_export.Meas_type_code = taric_measure_footnote.Meas_type_code
|
|
LEFT JOIN taric_duties_import ON taric_measure_footnote.Goods_code = taric_duties_import.Goods_code
|
|
AND taric_duties_import.Origin_code = taric_measure_footnote.Origin_code
|
|
AND taric_duties_import.Meas_type_code = taric_measure_footnote.Meas_type_code
|
|
WHERE taric_measure_footnote.Goods_code IN ({$codeString})
|
|
ORDER BY taric_measure_footnote.Origin_code DESC
|
|
LIMIT 1000;
|
|
SQL;
|
|
|
|
$query = new Builder($con);
|
|
|
|
$view->data['footnotes'] = $query->raw($sql)->execute()?->fetchAll() ?? [];
|
|
|
|
return $view;
|
|
}
|
|
|
|
/**
|
|
* Backend method to handle basic search request
|
|
*
|
|
* @param RequestAbstract $request Request
|
|
* @param ResponseAbstract $response Response
|
|
* @param array $data Generic data
|
|
*
|
|
* @return RenderableInterface
|
|
*
|
|
* @api
|
|
*
|
|
* @since 1.0.0
|
|
*/
|
|
public function viewHSCodeDashboard(RequestAbstract $request, ResponseAbstract $response, array $data = []) : RenderableInterface
|
|
{
|
|
$view = new View($this->app->l11nManager, $request, $response);
|
|
$view->setTemplate('/Modules/Customs/Theme/Backend/hscode-dashboard');
|
|
|
|
if (!$request->hasData('hscode')) {
|
|
return $view;
|
|
}
|
|
|
|
$con = new \phpOMS\DataStorage\Database\Connection\SQLiteConnection([
|
|
'db' => 'sqlite',
|
|
'database' => __DIR__ . '/../Data/customs.sqlite',
|
|
]);
|
|
|
|
$con->connect();
|
|
|
|
// Search codes that contain the keyword
|
|
$sql = <<<SQL
|
|
SELECT taric_good.Goods_code
|
|
FROM taric_good
|
|
WHERE taric_good.Language = "EN"
|
|
AND (taric_good.Description LIKE :description
|
|
OR taric_good.Goods_code LIKE :code)
|
|
ORDER BY taric_good.Goods_code ASC
|
|
LIMIT 100;
|
|
SQL;
|
|
|
|
$query = new Builder($con);
|
|
$query->bind(['value' => '%' . $request->getDataString('hscode') . '%'], ':description');
|
|
$query->bind(['value' => '%' . $request->getDataString('hscode') . '%'], ':code');
|
|
|
|
$temp = $query->raw($sql)->execute()?->fetchAll() ?? [];
|
|
|
|
if (($exactCount = \count($temp)) === 0) {
|
|
return $view;
|
|
}
|
|
|
|
// Sometimes sub-categories exist that don't contain the keyword but may be better
|
|
// For this reason we have to also load all the sub-categories
|
|
// Create code range by finding the last none-0 value and increasing that value by 1
|
|
// While that value is a 9 the value is set to 0 and the left number is increased by 1
|
|
$codeRanges = [];
|
|
$exactMatches = [];
|
|
|
|
foreach ($temp as $code) {
|
|
$exactMatches[] = $code['Goods_code'];
|
|
|
|
if ($exactCount > 50) {
|
|
continue;
|
|
}
|
|
|
|
$length = \strlen($code['Goods_code']);
|
|
$max = \max(
|
|
\strrpos($code['Goods_code'], '1', -3),
|
|
\strrpos($code['Goods_code'], '2', -3),
|
|
\strrpos($code['Goods_code'], '3', -3),
|
|
\strrpos($code['Goods_code'], '4', -3),
|
|
\strrpos($code['Goods_code'], '5', -3),
|
|
\strrpos($code['Goods_code'], '6', -3),
|
|
\strrpos($code['Goods_code'], '7', -3),
|
|
\strrpos($code['Goods_code'], '8', -3),
|
|
\strrpos($code['Goods_code'], '9', -3),
|
|
);
|
|
$maxCode = $code['Goods_code'];
|
|
$maxCode[$length - 1] = '0';
|
|
$maxCode[$length - 2] = '0';
|
|
|
|
while ($maxCode[$max] === '9') {
|
|
$maxCode[$max] = '0';
|
|
--$max;
|
|
}
|
|
|
|
$maxCode[$max] = ((int) $code['Goods_code'][$max]) + 1;
|
|
$codeRanges[] = '(taric_good.Goods_code >= "' . $code['Goods_code'] . '"'
|
|
. ' AND taric_good.Goods_code < "' . $maxCode . '")';
|
|
}
|
|
|
|
$codeRanges = empty($codeRanges)
|
|
? ''
|
|
: ' OR (' . \implode(' OR ', $codeRanges) . ')';
|
|
|
|
$exactMatches = '"' . \implode('","', $exactMatches) . '"';
|
|
|
|
$sql = <<<SQL
|
|
SELECT taric_good.Goods_code, taric_good.Indent, taric_good.Description
|
|
FROM taric_good
|
|
WHERE taric_good.Language = "EN"
|
|
AND (taric_good.Goods_code IN ({$exactMatches}) {$codeRanges})
|
|
ORDER BY taric_good.Goods_code ASC
|
|
LIMIT 1000;
|
|
SQL;
|
|
|
|
$query = new Builder($con);
|
|
$view->data['codes'] = $query->raw($sql)->execute()?->fetchAll() ?? [];
|
|
|
|
$con->close();
|
|
|
|
return $view;
|
|
}
|
|
}
|