Artifact ed8b6884a24a4cd2c02495d4d2a5448a06e63a15:


<?php

namespace Garradin\Entities\Accounting;

use DateTimeInterface;
use Garradin\Entity;
use Garradin\DB;
use Garradin\Utils;
use Garradin\UserException;
use Garradin\ValidationException;
use Garradin\Accounting\Charts;

class Account extends Entity
{
	const TABLE = 'acc_accounts';

	// Actif
	const ASSET = 1;

	// Passif
	const LIABILITY = 2;

	// Passif ou actif
	const ASSET_OR_LIABILITY = 3;

	// Produit
	const REVENUE = 4;

	// Charge
	const EXPENSE = 5;

	const POSITIONS_NAMES = [
		'',
		'Actif',
		'Passif',
		'Actif ou passif',
		'Produit',
		'Charge',
	];

	const TYPE_NONE = 0;
	const TYPE_REVENUE = 1;
	const TYPE_EXPENSE = 2;
	const TYPE_BANK = 3;
	const TYPE_CASH = 4;

	/**
	 * Outstanding transaction accounts (like cheque or card payments)
	 */
	const TYPE_OUTSTANDING = 5;

	const TYPE_ANALYTICAL = 6;
	const TYPE_VOLUNTEERING = 7;
	const TYPE_THIRD_PARTY = 8;

	const TYPE_OPENING = 9;
	const TYPE_CLOSING = 10;

	const TYPES_NAMES = [
		'',
		'Recettes',
		'Dépenses',
		'Banque',
		'Caisse',
		'Attente d\'encaissement',
		'Analytique',
		'Bénévolat',
		'Tiers',
		'Ouverture',
		'Clôture',
	];

	protected $id;
	protected $id_chart;
	protected $code;
	protected $label;
	protected $description;
	protected $position;
	protected $type;
	protected $user = 0;

	protected $_types = [
		'id'          => 'int',
		'id_chart'    => 'int',
		'code'        => 'string',
		'label'       => 'string',
		'description' => '?string',
		'position'    => 'int',
		'type'        => 'int',
		'user'        => 'int',
	];

	protected $_form_rules = [
		'code'        => 'required|string|alpha_num|max:10',
		'label'       => 'required|string|max:200',
		'description' => 'string|max:2000',
		'position'    => 'required|numeric|min:0',
		'type'        => 'required|numeric|min:0',
	];

	public function selfCheck(): void
	{
		$db = DB::getInstance();

		$this->assert(!empty($this->id_chart), 'Aucun plan comptable lié');

		$where = 'code = ? AND id_chart = ?';
		$where .= $this->exists() ? sprintf(' AND id != %d', $this->id()) : '';

		if ($db->test(self::TABLE, $where, $this->code, $this->id_chart)) {
			throw new ValidationException(sprintf('Le code "%s" est déjà utilisé par un autre compte.', $this->code));
		}

		$this->assert(array_key_exists($this->type, self::TYPES_NAMES), 'Type invalide');
		$this->assert(array_key_exists($this->position, self::POSITIONS_NAMES), 'Position invalide');
		$this->assert($this->user === 0 || $this->user === 1);

		parent::selfCheck();
	}

	public function getJournal(int $year_id)
	{
		$db = DB::getInstance();
		$sql = 'SELECT l.debit, l.credit, t.id, t.date, t.reference, l.reference AS line_reference, t.label, l.label AS line_label, l.reconciled
			FROM acc_transactions_lines l
			INNER JOIN acc_transactions t ON t.id = l.id_transaction
			WHERE l.id_account = ? AND t.id_year = ?
			ORDER BY t.date, t.id;';
		$rows = $db->get($sql, $this->id(), $year_id);

		$sum = 0;

		foreach ($rows as &$row) {
			$sum += ($row->credit - $row->debit);
			$row->running_sum = $sum;
			$row->date = \DateTime::createFromFormat('Y-m-d', $row->date);
		}

		return $rows;
	}

	public function getReconcileJournal(int $year_id, DateTimeInterface $start_date, DateTimeInterface $end_date)
	{
		if ($end_date < $start_date) {
			throw new ValidationException('La date de début ne peut être avant la date de fin.');
		}

		$db = DB::getInstance();
		$sql = 'SELECT l.debit, l.credit, t.id, t.date, t.reference, l.reference AS line_reference, t.label, l.label AS line_label, l.reconciled, l.id AS id_line
			FROM acc_transactions_lines l
			INNER JOIN acc_transactions t ON t.id = l.id_transaction
			WHERE l.id_account = ? AND t.id_year = ? AND t.date >= ? AND t.date <= ?
			ORDER BY t.date, t.id;';
		$rows = $db->iterate($sql, $this->id(), $year_id, $start_date->format('Y-m-d'), $end_date->format('Y-m-d'));

		$sum = $this->getSumAtDate($year_id, $start_date);

		$start_sum = false;

		foreach ($rows as $row) {
			if (!$start_sum) {
				yield ['sum' => $sum, 'date' => $start_date];
				$start_sum = true;
			}

			$row->date = \DateTime::createFromFormat('Y-m-d', $row->date);
			$sum += ($row->credit - $row->debit);
			$row->running_sum = $sum;

			yield $row;
		}

		yield ['sum' => $sum, 'date' => $end_date];
	}

	public function getDepositJournal(int $year_id): \Generator
	{
		$res = DB::getInstance()->iterate('SELECT l.debit, l.credit, t.id, t.date, t.reference, l.reference AS line_reference, t.label, l.label AS line_label, l.reconciled, l.id AS id_line, l.id_account
			FROM acc_transactions_lines l
			INNER JOIN acc_transactions t ON t.id = l.id_transaction
			WHERE t.id_year = ? AND l.id_account = ? AND l.credit = 0 AND NOT (t.status & ?)
			ORDER BY t.date, t.id;',
			$year_id, $this->id(), Transaction::STATUS_DEPOSIT);

		$sum = 0;

		foreach ($res as $row) {
			$row->date = \DateTime::createFromFormat('Y-m-d', $row->date);
			$sum += ($row->credit - $row->debit);
			$row->running_sum = $sum;
			yield $row;
		}
	}

	public function getSumAtDate(int $year_id, DateTimeInterface $date): int
	{
		return (int) DB::getInstance()->firstColumn('SELECT SUM(l.credit) - SUM(l.debit)
			FROM acc_transactions_lines l
			INNER JOIN acc_transactions t ON t.id = l.id_transaction
			wHERE l.id_account = ? AND t.id_year = ? AND t.date < ?
			ORDER BY t.date, t.id;', $this->id(), $year_id, $date->format('Y-m-d'));
	}

	public function importSimpleForm(array $translate_type_position, array $translate_type_codes, ?array $source = null)
	{
		if (null === $source) {
			$source = $_POST;
		}

		if (empty($source['type'])) {
			throw new UserException('Le type est obligatoire dans ce formulaire');
		}

		$type = (int) $source['type'];

		if (array_key_exists($type, $translate_type_position)) {
			$source['position'] = $translate_type_position[$type];
		}
		else {
			$source['position'] = self::ASSET_OR_LIABILITY;
		}

		if (array_key_exists($type, $translate_type_codes)) {
			$source['code'] = $translate_type_codes[$type];
		}

		$this->importForm($source);
	}

	public function importLimitedForm(?array $source = null)
	{
		if (null === $source) {
			$source = $_POST;
		}

		$data = array_intersect_key($source, array_flip(['type', 'description']));
		parent::import($data);
	}

	public function canDelete(): bool
	{
		return !DB::getInstance()->firstColumn(sprintf('SELECT 1 FROM %s WHERE id_account = ? LIMIT 1;', Line::TABLE), $this->id());
	}

	/**
	 * An account properties (position, label and code) can only be changed if:
	 * * it's either a user-created account or an account part of a user-created chart
	 * * has no transactions in a closed year
	 * @return bool
	 */
	public function canEdit(): bool
	{
		$db = DB::getInstance();
		$sql = sprintf('SELECT 1 FROM %s l
			INNER JOIN %s t ON t.id = l.id_transaction
			INNER JOIN %s y ON y.id = t.id_year
			WHERE l.id_account = ? AND y.closed = 1
			LIMIT 1;', Line::TABLE, Transaction::TABLE, Year::TABLE);
		$has_transactions_in_closed_year = $db->firstColumn($sql, $this->id());

		if ($has_transactions_in_closed_year) {
			return false;
		}

		if ($this->user) {
			return true;
		}

		return $db->test(Chart::TABLE, 'id = ? AND code IS NULL', $this->id_chart);
	}

	public function chart(): Chart
	{
		return Charts::get($this->id_chart);
	}
}