477 lines
16 KiB
PHP
477 lines
16 KiB
PHP
<?php
|
|
|
|
namespace Modules\Accounting\Services;
|
|
|
|
use App\Business;
|
|
use App\Currency;
|
|
use App\HoldingEntity;
|
|
use Carbon\Carbon;
|
|
use Illuminate\Support\Collection;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Support\Facades\Schema;
|
|
use Modules\Accounting\Entities\AccountingExchangeRate;
|
|
use Modules\Accounting\Utils\AccountingUtil;
|
|
|
|
class ExchangeRateService
|
|
{
|
|
public function __construct(
|
|
protected AccountingUtil $accountingUtil,
|
|
protected ExternalExchangeRateProvider $externalProvider
|
|
) {}
|
|
|
|
public function listRates(int $businessId, ?string $date = null): Collection
|
|
{
|
|
if (! Schema::hasTable('accounting_exchange_rates')) {
|
|
return collect();
|
|
}
|
|
|
|
$query = AccountingExchangeRate::where('business_id', $businessId)->orderByDesc('rate_date');
|
|
|
|
if ($date) {
|
|
$query->whereDate('rate_date', $date);
|
|
}
|
|
|
|
return $query->limit(200)->get();
|
|
}
|
|
|
|
public function upsertRate(
|
|
int $businessId,
|
|
string $fromCurrency,
|
|
string $toCurrency,
|
|
float $rate,
|
|
string $date,
|
|
string $source = 'manual',
|
|
string $rateType = 'manual'
|
|
): AccountingExchangeRate {
|
|
$from = strtoupper($fromCurrency);
|
|
$to = strtoupper($toCurrency);
|
|
$normalizedRate = max(1.0E-10, $rate);
|
|
|
|
return AccountingExchangeRate::updateOrCreate(
|
|
[
|
|
'business_id' => $businessId,
|
|
'from_currency' => $from,
|
|
'to_currency' => $to,
|
|
'rate_date' => $date,
|
|
'rate_type' => $rateType,
|
|
],
|
|
[
|
|
'rate' => $normalizedRate,
|
|
'source' => $source,
|
|
]
|
|
);
|
|
}
|
|
|
|
public function getRateByType(int $businessId, string $fromCurrency, string $toCurrency, string $rateType, ?string $date = null): ?float
|
|
{
|
|
$from = strtoupper($fromCurrency);
|
|
$to = strtoupper($toCurrency);
|
|
|
|
if ($from === $to) {
|
|
return 1.0;
|
|
}
|
|
|
|
if (! Schema::hasTable('accounting_exchange_rates')) {
|
|
return null;
|
|
}
|
|
|
|
$asOf = $date ?: now()->toDateString();
|
|
$direct = $this->lookupRate($businessId, $from, $to, $asOf, $rateType);
|
|
|
|
return $direct ?? $this->getRate($businessId, $from, $to, $asOf);
|
|
}
|
|
|
|
public function dualRateComparison(int $businessId, string $fromCurrency, string $toCurrency, ?string $date = null): array
|
|
{
|
|
$market = $this->getRateByType($businessId, $fromCurrency, $toCurrency, 'market', $date);
|
|
$official = $this->getRateByType($businessId, $fromCurrency, $toCurrency, 'official', $date);
|
|
|
|
$variance = null;
|
|
$variancePct = null;
|
|
if ($market !== null && $official !== null && $official > 0) {
|
|
$variance = $market - $official;
|
|
$variancePct = round(($variance / $official) * 100, 2);
|
|
}
|
|
|
|
return [
|
|
'from' => strtoupper($fromCurrency),
|
|
'to' => strtoupper($toCurrency),
|
|
'market_rate' => $market,
|
|
'official_rate' => $official,
|
|
'variance' => $variance,
|
|
'variance_pct' => $variancePct,
|
|
];
|
|
}
|
|
|
|
public function getRate(int $businessId, string $fromCurrency, string $toCurrency, ?string $date = null): ?float
|
|
{
|
|
$from = strtoupper($fromCurrency);
|
|
$to = strtoupper($toCurrency);
|
|
|
|
if ($from === $to) {
|
|
return 1.0;
|
|
}
|
|
|
|
if (! Schema::hasTable('accounting_exchange_rates')) {
|
|
return null;
|
|
}
|
|
|
|
$asOf = $date ?: now()->toDateString();
|
|
|
|
$direct = $this->lookupRate($businessId, $from, $to, $asOf);
|
|
if ($direct !== null) {
|
|
return $direct;
|
|
}
|
|
|
|
$inverse = $this->lookupRate($businessId, $to, $from, $asOf);
|
|
if ($inverse !== null && $inverse > 0) {
|
|
return 1 / $inverse;
|
|
}
|
|
|
|
$base = $this->baseCurrencyCode($businessId);
|
|
|
|
if ($from !== $base && $to !== $base) {
|
|
$fromToBase = $this->getRate($businessId, $from, $base, $asOf);
|
|
$baseToTo = $this->getRate($businessId, $base, $to, $asOf);
|
|
|
|
if ($fromToBase !== null && $baseToTo !== null) {
|
|
return $fromToBase * $baseToTo;
|
|
}
|
|
}
|
|
|
|
return null;
|
|
}
|
|
|
|
public function resolveEntityRate(
|
|
int $businessId,
|
|
string $entityCurrency,
|
|
string $targetCurrency,
|
|
array $entityMeta = [],
|
|
?string $date = null
|
|
): float {
|
|
$entityCurrency = strtoupper($entityCurrency);
|
|
$target = strtoupper($targetCurrency);
|
|
$base = $this->baseCurrencyCode($businessId);
|
|
|
|
if ($entityCurrency === $target) {
|
|
return 1.0;
|
|
}
|
|
|
|
$tableRate = $this->getRate($businessId, $entityCurrency, $target, $date);
|
|
if ($tableRate !== null) {
|
|
return max(1.0E-10, $tableRate);
|
|
}
|
|
|
|
$explicitByTarget = (float) data_get($entityMeta, 'exchange_rates.'.$target, 0);
|
|
if ($explicitByTarget > 0) {
|
|
return $explicitByTarget;
|
|
}
|
|
|
|
$toBase = (float) data_get($entityMeta, 'exchange_rate_to_base', 0);
|
|
$fromBase = (float) data_get($entityMeta, 'exchange_rate_from_base', 0);
|
|
|
|
if ($target === $base && $toBase > 0) {
|
|
return $toBase;
|
|
}
|
|
|
|
if ($entityCurrency === $base && $fromBase > 0 && $target !== $base) {
|
|
return $fromBase;
|
|
}
|
|
|
|
if ($toBase > 0 && $fromBase > 0) {
|
|
return $toBase * $fromBase;
|
|
}
|
|
|
|
return 1.0;
|
|
}
|
|
|
|
public function syncForBusiness(int $businessId, ?string $date = null, bool $pushToEntities = true): array
|
|
{
|
|
if (! Schema::hasTable('accounting_exchange_rates')) {
|
|
return ['upserted' => 0, 'entities_updated' => 0, 'api_fetched' => 0];
|
|
}
|
|
|
|
$rateDate = $date ?: now()->toDateString();
|
|
$base = $this->baseCurrencyCode($businessId);
|
|
$currencies = $this->discoverCurrencies($businessId, $base);
|
|
$upserted = 0;
|
|
$apiFetched = $this->syncFromExternalApi($businessId, $base, $currencies, $rateDate);
|
|
$upserted += $apiFetched;
|
|
$upserted += $this->syncIranDualRates($businessId, $base, $currencies, $rateDate);
|
|
|
|
foreach ($currencies as $currency) {
|
|
if ($currency === $base) {
|
|
continue;
|
|
}
|
|
|
|
$rate = $this->deriveRate($businessId, $currency, $base, $rateDate);
|
|
if ($rate === null) {
|
|
continue;
|
|
}
|
|
|
|
$existing = AccountingExchangeRate::where('business_id', $businessId)
|
|
->where('from_currency', $currency)
|
|
->where('to_currency', $base)
|
|
->whereDate('rate_date', $rateDate)
|
|
->where('source', 'api')
|
|
->exists();
|
|
|
|
if ($existing) {
|
|
continue;
|
|
}
|
|
|
|
$this->upsertRate($businessId, $currency, $base, $rate, $rateDate, 'sync', 'sync');
|
|
$upserted++;
|
|
|
|
$inverse = $rate > 0 ? (1 / $rate) : 1;
|
|
$this->upsertRate($businessId, $base, $currency, $inverse, $rateDate, 'sync', 'sync');
|
|
$upserted++;
|
|
}
|
|
|
|
$entitiesUpdated = 0;
|
|
if ($pushToEntities) {
|
|
$entitiesUpdated = $this->pushRatesToEntities($businessId, $rateDate, $base);
|
|
}
|
|
|
|
return [
|
|
'upserted' => $upserted,
|
|
'entities_updated' => $entitiesUpdated,
|
|
'api_fetched' => $apiFetched,
|
|
];
|
|
}
|
|
|
|
public function syncFromExternalApi(int $businessId, string $baseCurrency, array $currencies, string $rateDate): int
|
|
{
|
|
$settings = $this->accountingUtil->getAccountingSettings($businessId);
|
|
|
|
if (empty($settings['fx_api_enabled'])) {
|
|
return 0;
|
|
}
|
|
|
|
$provider = $settings['fx_api_provider'] ?? config('accounting.fx_api.default_provider', 'open_er_api');
|
|
$apiKey = $settings['fx_api_key'] ?? null;
|
|
|
|
$rates = $this->externalProvider->fetchRatesToBase($baseCurrency, $currencies, $provider, $apiKey);
|
|
$upserted = 0;
|
|
|
|
foreach ($rates as $currency => $rate) {
|
|
$this->upsertRate($businessId, $currency, $baseCurrency, $rate, $rateDate, 'api', 'api');
|
|
$upserted++;
|
|
|
|
$inverse = $rate > 0 ? (1 / $rate) : 1;
|
|
$this->upsertRate($businessId, $baseCurrency, $currency, $inverse, $rateDate, 'api', 'api');
|
|
$upserted++;
|
|
}
|
|
|
|
return $upserted;
|
|
}
|
|
|
|
public function syncIranDualRates(int $businessId, string $baseCurrency, array $currencies, string $rateDate): int
|
|
{
|
|
if (strtoupper($baseCurrency) !== 'IRR') {
|
|
return 0;
|
|
}
|
|
|
|
$dual = $this->externalProvider->fetchIranDualRates();
|
|
$upserted = 0;
|
|
|
|
foreach (['market', 'official'] as $type) {
|
|
$rates = (array) ($dual[$type] ?? []);
|
|
foreach ($currencies as $currency) {
|
|
$currency = strtoupper($currency);
|
|
if ($currency === 'IRR' || empty($rates[$currency])) {
|
|
continue;
|
|
}
|
|
|
|
$rate = (float) $rates[$currency];
|
|
$this->upsertRate($businessId, $currency, 'IRR', $rate, $rateDate, 'iran_'.$type, $type);
|
|
$upserted++;
|
|
$this->upsertRate($businessId, 'IRR', $currency, $rate > 0 ? (1 / $rate) : 1, $rateDate, 'iran_'.$type, $type);
|
|
$upserted++;
|
|
}
|
|
}
|
|
|
|
return $upserted;
|
|
}
|
|
|
|
public function pushRatesToEntities(int $businessId, ?string $date = null, ?string $targetCurrency = null): int
|
|
{
|
|
$rateDate = $date ?: now()->toDateString();
|
|
$target = strtoupper($targetCurrency ?: $this->baseCurrencyCode($businessId));
|
|
$updated = 0;
|
|
|
|
$entities = HoldingEntity::where('business_id', $businessId)->where('is_active', true)->get();
|
|
|
|
foreach ($entities as $entity) {
|
|
$entityCurrency = strtoupper((string) data_get($entity->meta, 'currency_code', $target));
|
|
$rate = $this->resolveEntityRate($businessId, $entityCurrency, $target, (array) $entity->meta, $rateDate);
|
|
|
|
$meta = (array) ($entity->meta ?? []);
|
|
$exchangeRates = (array) data_get($meta, 'exchange_rates', []);
|
|
$exchangeRates[$target] = $rate;
|
|
$meta['exchange_rates'] = $exchangeRates;
|
|
$meta['exchange_rate_synced_at'] = now()->toDateTimeString();
|
|
|
|
$entity->meta = $meta;
|
|
$entity->save();
|
|
$updated++;
|
|
}
|
|
|
|
return $updated;
|
|
}
|
|
|
|
public function staleRateSummary(int $businessId, int $maxAgeDays = 2): array
|
|
{
|
|
$base = $this->baseCurrencyCode($businessId);
|
|
$currencies = array_values(array_filter($this->discoverCurrencies($businessId, $base), fn ($c) => $c !== $base));
|
|
|
|
if (empty($currencies)) {
|
|
return ['stale_count' => 0, 'missing_count' => 0, 'max_age_days' => 0, 'pairs' => [], 'total_issues' => 0];
|
|
}
|
|
|
|
$cutoff = Carbon::today()->subDays($maxAgeDays)->toDateString();
|
|
$staleCount = 0;
|
|
$missingCount = 0;
|
|
$maxAgeDaysFound = 0;
|
|
$pairs = [];
|
|
|
|
foreach ($currencies as $currency) {
|
|
$latest = null;
|
|
if (Schema::hasTable('accounting_exchange_rates')) {
|
|
$latest = AccountingExchangeRate::where('business_id', $businessId)
|
|
->where('from_currency', $currency)
|
|
->where('to_currency', $base)
|
|
->orderByDesc('rate_date')
|
|
->first();
|
|
}
|
|
|
|
if (! $latest) {
|
|
$missingCount++;
|
|
$pairs[] = $currency.'→'.$base;
|
|
continue;
|
|
}
|
|
|
|
$rateDate = optional($latest->rate_date)->toDateString();
|
|
if ($rateDate < $cutoff) {
|
|
$staleCount++;
|
|
$pairs[] = $currency.'→'.$base;
|
|
$age = Carbon::parse($rateDate)->diffInDays(Carbon::today());
|
|
$maxAgeDaysFound = max($maxAgeDaysFound, $age);
|
|
}
|
|
}
|
|
|
|
return [
|
|
'stale_count' => $staleCount,
|
|
'missing_count' => $missingCount,
|
|
'max_age_days' => $maxAgeDaysFound,
|
|
'pairs' => $pairs,
|
|
'total_issues' => $staleCount + $missingCount,
|
|
];
|
|
}
|
|
|
|
protected function lookupRate(int $businessId, string $from, string $to, string $asOf, ?string $rateType = null): ?float
|
|
{
|
|
$query = AccountingExchangeRate::where('business_id', $businessId)
|
|
->where('from_currency', $from)
|
|
->where('to_currency', $to)
|
|
->whereDate('rate_date', '<=', $asOf);
|
|
|
|
if ($rateType) {
|
|
$query->where('rate_type', $rateType);
|
|
}
|
|
|
|
$row = $query->orderByDesc('rate_date')->first();
|
|
|
|
return $row ? (float) $row->rate : null;
|
|
}
|
|
|
|
protected function baseCurrencyCode(int $businessId): string
|
|
{
|
|
$business = Business::with('currency')->find($businessId);
|
|
|
|
return strtoupper((string) optional(optional($business)->currency)->code ?: 'IRR');
|
|
}
|
|
|
|
protected function discoverCurrencies(int $businessId, string $base): array
|
|
{
|
|
$codes = collect([$base]);
|
|
|
|
$entities = HoldingEntity::where('business_id', $businessId)->where('is_active', true)->get();
|
|
foreach ($entities as $entity) {
|
|
$code = strtoupper((string) data_get($entity->meta, 'currency_code', ''));
|
|
if ($code !== '') {
|
|
$codes->push($code);
|
|
}
|
|
}
|
|
|
|
$business = Business::find($businessId);
|
|
if (! empty($business?->purchase_currency_id)) {
|
|
$purchaseCode = Currency::where('id', $business->purchase_currency_id)->value('code');
|
|
if ($purchaseCode) {
|
|
$codes->push(strtoupper($purchaseCode));
|
|
}
|
|
}
|
|
|
|
if (Schema::hasTable('accounting_exchange_rates')) {
|
|
$stored = AccountingExchangeRate::where('business_id', $businessId)
|
|
->select('from_currency', 'to_currency')
|
|
->get();
|
|
|
|
foreach ($stored as $row) {
|
|
$codes->push(strtoupper($row->from_currency));
|
|
$codes->push(strtoupper($row->to_currency));
|
|
}
|
|
}
|
|
|
|
return $codes->filter()->unique()->values()->all();
|
|
}
|
|
|
|
protected function deriveRate(int $businessId, string $fromCurrency, string $baseCurrency, string $rateDate): ?float
|
|
{
|
|
$existing = $this->lookupRate($businessId, $fromCurrency, $baseCurrency, $rateDate);
|
|
if ($existing !== null) {
|
|
return $existing;
|
|
}
|
|
|
|
$fromEntityMeta = HoldingEntity::where('business_id', $businessId)
|
|
->where('is_active', true)
|
|
->get()
|
|
->first(function ($entity) use ($fromCurrency) {
|
|
return strtoupper((string) data_get($entity->meta, 'currency_code', '')) === $fromCurrency;
|
|
});
|
|
|
|
if ($fromEntityMeta) {
|
|
$metaRate = (float) data_get($fromEntityMeta->meta, 'exchange_rate_to_base', 0);
|
|
if ($metaRate > 0) {
|
|
return $metaRate;
|
|
}
|
|
}
|
|
|
|
$business = Business::with('currency')->find($businessId);
|
|
$purchaseCode = null;
|
|
if (! empty($business?->purchase_currency_id)) {
|
|
$purchaseCode = strtoupper((string) Currency::where('id', $business->purchase_currency_id)->value('code'));
|
|
}
|
|
|
|
if ($purchaseCode === $fromCurrency && (float) $business->p_exchange_rate > 0) {
|
|
return (float) $business->p_exchange_rate;
|
|
}
|
|
|
|
if (Schema::hasTable('transactions')) {
|
|
$avg = DB::table('transactions')
|
|
->where('business_id', $businessId)
|
|
->where('exchange_rate', '>', 0)
|
|
->where('exchange_rate', '!=', 1)
|
|
->whereDate('transaction_date', '>=', Carbon::parse($rateDate)->subDays(30)->toDateString())
|
|
->whereDate('transaction_date', '<=', $rateDate)
|
|
->avg('exchange_rate');
|
|
|
|
if ($avg && (float) $avg > 0 && $purchaseCode === $fromCurrency) {
|
|
return (float) $avg;
|
|
}
|
|
}
|
|
|
|
return null;
|
|
}
|
|
}
|