ultimatepos/Modules/Accounting/Services/SubsidiaryLedgerService.php

<?php

namespace Modules\Accounting\Services;

use Illuminate\Support\Facades\DB;
use Modules\Accounting\Entities\AccountingAnalyticalDimension;
use Modules\Accounting\Utils\AccountingUtil;

class SubsidiaryLedgerService
{
    public function __construct(protected AccountingUtil $accountingUtil) {}

    public function byDimension(int $businessId, string $dimensionType, string $startDate, string $endDate): array
    {
        $balanceFormula = $this->accountingUtil->balanceFormula('aa', 'aat');
        $column = match ($dimensionType) {
            'project' => 'project_id',
            'department' => 'cost_center_id',
            default => 'cost_center_id',
        };

        $rows = DB::table('accounting_analytical_dimensions as dim')
            ->leftJoin('accounting_accounts_transactions as aat', function ($join) use ($column, $startDate, $endDate) {
                $join->on('aat.'.$column, '=', 'dim.id')
                    ->whereDate('aat.operation_date', '>=', $startDate)
                    ->whereDate('aat.operation_date', '<=', $endDate);
            })
            ->leftJoin('accounting_accounts as aa', 'aa.id', '=', 'aat.accounting_account_id')
            ->where('dim.business_id', $businessId)
            ->where('dim.dimension_type', $dimensionType)
            ->where('dim.is_active', true)
            ->groupBy('dim.id', 'dim.code', 'dim.name')
            ->select(
                'dim.id',
                'dim.code',
                'dim.name',
                DB::raw('COUNT(aat.id) as line_count'),
                DB::raw("COALESCE(SUM(IF(aat.type='debit', aat.amount, 0)), 0) as total_debit"),
                DB::raw("COALESCE(SUM(IF(aat.type='credit', aat.amount, 0)), 0) as total_credit")
            )
            ->get();

        return $rows->map(function ($row) {
            $row->net_balance = (float) $row->total_debit - (float) $row->total_credit;

            return $row;
        })->all();
    }

    public function byHoldingEntity(int $businessId, string $startDate, string $endDate): array
    {
        if (! \Illuminate\Support\Facades\Schema::hasColumn('accounting_accounts_transactions', 'holding_entity_id')) {
            return [];
        }

        $rows = DB::table('holding_entities as he')
            ->leftJoin('accounting_accounts_transactions as aat', function ($join) use ($startDate, $endDate) {
                $join->on('aat.holding_entity_id', '=', 'he.id')
                    ->whereDate('aat.operation_date', '>=', $startDate)
                    ->whereDate('aat.operation_date', '<=', $endDate);
            })
            ->leftJoin('accounting_accounts as aa', function ($join) use ($businessId) {
                $join->on('aa.id', '=', 'aat.accounting_account_id')
                    ->where('aa.business_id', $businessId);
            })
            ->where('he.business_id', $businessId)
            ->where('he.is_active', true)
            ->whereNull('he.deleted_at')
            ->groupBy('he.id', 'he.code', 'he.name')
            ->select(
                'he.id',
                DB::raw('he.code as code'),
                DB::raw('he.name as name'),
                DB::raw('COUNT(aat.id) as line_count'),
                DB::raw("COALESCE(SUM(IF(aat.type='debit', aat.amount, 0)), 0) as total_debit"),
                DB::raw("COALESCE(SUM(IF(aat.type='credit', aat.amount, 0)), 0) as total_credit")
            )
            ->get();

        return $rows->map(function ($row) {
            $row->net_balance = (float) $row->total_debit - (float) $row->total_credit;

            return $row;
        })->all();
    }

    public function dimensionTypes(int $businessId): array
    {
        return AccountingAnalyticalDimension::where('business_id', $businessId)
            ->where('is_active', true)
            ->distinct()
            ->pluck('dimension_type')
            ->all();
    }

    /**
     * Contact subsidiary (تفصیلی اشخاص) — GL lines when mapped, else operational invoices/payments.
     */
    public function byContact(int $businessId, string $startDate, string $endDate, ?string $contactType = null): array
    {
        $query = DB::table('contacts as c')
            ->where('c.business_id', $businessId)
            ->whereNull('c.deleted_at');

        if ($contactType === 'customer') {
            $query->whereIn('c.type', ['customer', 'both']);
        } elseif ($contactType === 'supplier') {
            $query->whereIn('c.type', ['supplier', 'both']);
        }

        $rows = $query
            ->leftJoin('transactions as t', function ($join) use ($startDate, $endDate) {
                $join->on('t.contact_id', '=', 'c.id')
                    ->where('t.status', 'final')
                    ->whereDate('t.transaction_date', '>=', $startDate)
                    ->whereDate('t.transaction_date', '<=', $endDate);
            })
            ->leftJoin('accounting_accounts_transactions as aat', 'aat.transaction_id', '=', 't.id')
            ->groupBy('c.id', 'c.name', 'c.contact_id', 'c.type')
            ->select(
                'c.id',
                DB::raw('COALESCE(c.contact_id, CAST(c.id AS CHAR)) as code'),
                'c.name',
                'c.type',
                DB::raw('COUNT(DISTINCT t.id) as transaction_count'),
                DB::raw("COALESCE(SUM(IF(aat.id IS NOT NULL AND aat.type='debit', aat.amount, 0)), 0) as gl_debit"),
                DB::raw("COALESCE(SUM(IF(aat.id IS NOT NULL AND aat.type='credit', aat.amount, 0)), 0) as gl_credit"),
                DB::raw("COALESCE(SUM(IF(aat.id IS NULL AND t.type IN ('sell','opening_balance'), t.final_total, 0)), 0) as op_debit"),
                DB::raw("COALESCE(SUM(IF(aat.id IS NULL AND t.type IN ('purchase','expense'), t.final_total, 0)), 0) as op_credit")
            )
            ->havingRaw('transaction_count > 0 OR gl_debit > 0 OR gl_credit > 0')
            ->orderBy('c.name')
            ->get();

        return $rows->map(function ($row) {
            $row->total_debit = (float) $row->gl_debit + (float) $row->op_debit;
            $row->total_credit = (float) $row->gl_credit + (float) $row->op_credit;
            $row->net_balance = $row->total_debit - $row->total_credit;

            return $row;
        })->all();
    }

    public function contactTypes(): array
    {
        return ['customer', 'supplier'];
    }
}