<?php
namespace Garradin\Accounting;
use Garradin\Entities\Accounting\Account;
use Garradin\Entities\Accounting\Line;
use Garradin\Entities\Accounting\Transaction;
use Garradin\Utils;
use Garradin\DB;
use KD2\DB\EntityManager;
class Reports
{
static public function getWhereClause(array $criterias): string
{
$where = [];
if (!empty($criterias['year'])) {
$where[] = sprintf('t.id_year = %d', $criterias['year']);
}
if (!empty($criterias['position'])) {
$db = DB::getInstance();
$where[] = $db->where('position', $criterias['position']);
}
if (!empty($criterias['exclude_position'])) {
$db = DB::getInstance();
$where[] = $db->where('position', 'NOT IN', $criterias['exclude_position']);
}
if (!empty($criterias['type'])) {
$criterias['type'] = array_map('intval', (array)$criterias['type']);
$where[] = sprintf('a.type IN (%s)', implode(',', $criterias['type']));
}
if (!empty($criterias['exclude_type'])) {
$criterias['exclude_type'] = array_map('intval', (array)$criterias['exclude_type']);
$where[] = sprintf('a.type NOT IN (%s)', implode(',', $criterias['exclude_type']));
}
if (!empty($criterias['user'])) {
$where[] = sprintf('t.id IN (SELECT id_transaction FROM acc_transactions_users WHERE id_user = %d)', $criterias['user']);
}
if (!empty($criterias['creator'])) {
$where[] = sprintf('t.id_creator = %d', $criterias['creator']);
}
if (!empty($criterias['service_user'])) {
$where[] = sprintf('t.id IN (SELECT tu.id_transaction FROM acc_transactions_users tu WHERE id_service_user = %d)', $criterias['service_user']);
}
if (!empty($criterias['analytical'])) {
$where[] = sprintf('l.id_analytical = %d', $criterias['analytical']);
}
if (!count($where)) {
throw new \LogicException('Unknown criteria');
}
return implode(' AND ', $where);
}
/**
* Return account sums per year or per account
* @param bool $order_year If true will return accounts grouped by year, if false it will return years grouped by account
*/
static public function getAnalyticalSums(bool $by_year = false): \Generator
{
$sql = 'SELECT a.label AS account_label, a.description AS account_description, a.id AS id_account,
y.id AS id_year, y.label AS year_label, y.start_date, y.end_date,
SUM(l.credit - l.debit) AS sum, SUM(l.credit) AS credit, SUM(l.debit) AS debit
FROM acc_transactions_lines l
INNER JOIN acc_transactions t ON t.id = l.id_transaction
INNER JOIN acc_accounts a ON a.id = l.id_analytical
INNER JOIN acc_years y ON y.id = t.id_year
GROUP BY %s
ORDER BY %s;';
if ($by_year) {
$group = 'y.id, a.id';
$order = 'y.start_date DESC, a.label COLLATE NOCASE';
}
else {
$group = 'a.id, y.id';
$order = 'a.label COLLATE NOCASE, y.id';
}
$sql = sprintf($sql, $group, $order);
$current = null;
foreach (DB::getInstance()->iterate($sql) as $row) {
$id = $by_year ? $row->id_year : $row->id_account;
if (null !== $current && $current->id !== $id) {
$current->items[] = (object) [
'label' => 'Total',
'credit' => $current->credit,
'debit' => $current->debit,
'sum' => $current->sum,
'id_account' => $by_year ? null : $current->id,
'id_year' => $by_year ? $current->id : null,
];
yield $current;
$current = null;
}
if (null === $current) {
$current = (object) [
'id' => $by_year ? $row->id_year : $row->id_account,
'label' => $by_year ? $row->year_label : $row->account_label,
'description' => !$by_year ? $row->account_description : null,
'credit' => 0,
'debit' => 0,
'sum' => 0,
'items' => []
];
}
$row->label = !$by_year ? $row->year_label : $row->account_label;
$current->items[] = $row;
$current->credit += $row->credit;
$current->debit += $row->debit;
$current->sum += $row->sum;
}
if ($current === null) {
return;
}
$current->items[] = (object) [
'label' => 'Total',
'credit' => $current->credit,
'debit' => $current->debit,
'sum' => $current->sum,
'id_account' => $by_year ? null : $row->id_account,
'id_year' => $by_year ? $row->id_year : null,
];
yield $current;
}
static public function getSumsByInterval(array $criterias, int $interval)
{
$where = self::getWhereClause($criterias);
$where_interval = !empty($criterias['year']) ? sprintf(' WHERE id_year = %d', $criterias['year']) : '';
$db = DB::getInstance();
$sql = sprintf('SELECT
strftime(\'%%s\', MIN(date)) / %d AS start_interval,
strftime(\'%%s\', MAX(date)) / %1$d AS end_interval
FROM acc_transactions %s;',
$interval, $where_interval);
extract((array)$db->first($sql));
$out = array_fill_keys(range($start_interval, $end_interval), 0);
$sql = sprintf('SELECT strftime(\'%%s\', t.date) / %d AS interval, SUM(l.credit) - SUM(l.debit) AS sum, t.id_year
FROM acc_transactions t
INNER JOIN acc_transactions_lines l ON l.id_transaction = t.id
INNER JOIN acc_accounts a ON a.id = l.id_account
WHERE %s
GROUP BY %s ORDER BY %3$s;', $interval, $where, isset($criterias['year']) ? 'interval' : 't.id_year, interval');
$data = $db->getGrouped($sql);
$sum = 0;
$year = null;
foreach ($out as $k => &$v) {
if (array_key_exists($k, $data)) {
$row = $data[$k];
if ($row->id_year != $year) {
$sum = 0;
$year = $row->id_year;
}
$sum += $data[$k]->sum;
}
$v = $sum;
}
unset($v);
return $out;
}
static public function getResult(array $criterias): int
{
$where = self::getWhereClause($criterias);
$sql = sprintf('SELECT SUM(l.credit) - SUM(l.debit)
FROM %s l
INNER JOIN %s t ON t.id = l.id_transaction
INNER JOIN %s a ON a.id = l.id_account
WHERE %s AND a.position = ?;',
Line::TABLE, Transaction::TABLE, Account::TABLE, $where);
$db = DB::getInstance();
$a = $db->firstColumn($sql, Account::REVENUE);
$b = $db->firstColumn($sql, Account::EXPENSE);
return (int)$a - abs((int)$b);
}
static public function getClosingSumsWithAccounts(array $criterias, ?string $order = null, bool $reverse = false): array
{
$where = self::getWhereClause($criterias);
$order = $order ?: 'a.code COLLATE NOCASE';
$reverse = $reverse ? '* - 1' : '';
// Find sums, link them to accounts
$sql = sprintf('SELECT a.id, a.code, a.label, a.position, SUM(l.credit) AS credit, SUM(l.debit) AS debit,
SUM(l.credit - l.debit) %s AS sum
FROM %s l
INNER JOIN %s t ON t.id = l.id_transaction
INNER JOIN %s a ON a.id = l.id_account
WHERE %s
GROUP BY l.id_account
HAVING sum != 0
ORDER BY %s;',
$reverse, Line::TABLE, Transaction::TABLE, Account::TABLE, $where, $order);
return DB::getInstance()->getGrouped($sql);
}
static public function getBalanceSheet(array $criterias): array
{
$out = [
Account::ASSET => [],
Account::LIABILITY => [],
'sums' => [
Account::ASSET => 0,
Account::LIABILITY => 0,
],
];
$position_criteria = ['position' => [Account::ASSET, Account::LIABILITY, Account::ASSET_OR_LIABILITY]];
$list = self::getClosingSumsWithAccounts($criterias + $position_criteria);
foreach ($list as $row) {
if ($row->sum == 0) {
// Ignore empty accounts
continue;
}
$position = $row->position;
if ($position == Account::ASSET_OR_LIABILITY) {
$position = $row->sum < 0 ? Account::ASSET : Account::LIABILITY;
$row->sum = abs($row->sum);
}
elseif ($position == Account::ASSET) {
// reverse number for assets
$row->sum *= -1;
}
$out[$position][] = $row;
}
$result = self::getResult($criterias);
$out[Account::LIABILITY][] = (object) [
'id' => null,
'label' => $result > 0 ? 'Résultat de l\'exercice courant (excédent)' : 'Résultat de l\'exercice courant (perte)',
'sum' => $result,
];
// Calculate the total sum for assets and liabilities
foreach ($out as $position => $rows) {
if ($position == 'sums') {
continue;
}
$sum = 0;
foreach ($rows as $row) {
$sum += $row->sum;
}
$out['sums'][$position] = $sum;
}
return $out;
}
/**
* Return list of favorite accounts (accounts with a type), grouped by type, with their current sum
* @return \Generator list of accounts grouped by type
*/
static public function getClosingSumsFavoriteAccounts(array $criterias): \Generator
{
$where = self::getWhereClause($criterias);
$sql = sprintf('SELECT a.id, a.code, a.label, a.description, a.type,
SUM(l.credit) - SUM(l.debit) AS sum
FROM %s a
INNER JOIN %s t ON t.id = l.id_transaction
INNER JOIN %s l ON a.id = l.id_account
WHERE a.type != 0 AND %s
GROUP BY l.id_account
ORDER BY a.type, a.code COLLATE NOCASE;', Account::TABLE, Transaction::TABLE, Line::TABLE, $where);
$group = null;
foreach (DB::getInstance()->iterate($sql) as $row) {
if (null !== $group && $row->type !== $group->type) {
yield $group;
$group = null;
}
if (null === $group) {
$group = (object) [
'label' => Account::TYPES_NAMES[$row->type],
'type' => $row->type,
'accounts' => []
];
}
$reverse = Account::isReversed($row->type) ? -1 : 1;
$row->sum *= $reverse;
$group->accounts[] = $row;
}
if (null !== $group) {
yield $group;
}
}
/**
* Grand livre
*/
static public function getGeneralLedger(array $criterias): \Generator
{
$where = self::getWhereClause($criterias);
$db = DB::getInstance();
$sql = sprintf('SELECT t.id_year, l.id_account, l.debit, l.credit, t.id, t.date, t.reference, l.reference AS line_reference, t.label, l.label AS line_label
FROM acc_transactions t
INNER JOIN acc_transactions_lines l ON l.id_transaction = t.id
INNER JOIN acc_accounts a ON a.id = l.id_account
WHERE %s
ORDER BY a.code COLLATE NOCASE, t.date, t.id;', $where);
$account = null;
$debit = $credit = 0;
$accounts = null;
foreach ($db->iterate($sql) as $row) {
if (null === $accounts) {
$accounts = $db->getGrouped('SELECT id, code, label FROM acc_accounts WHERE id_chart = (SELECT id_chart FROM acc_years WHERE id = ?);', $row->id_year);
}
if (null !== $account && $account->id != $row->id_account) {
yield $account;
$account = null;
}
if (null === $account) {
$account = (object) [
'code' => $accounts[$row->id_account]->code,
'label' => $accounts[$row->id_account]->label,
'id' => $row->id_account,
'id_year' => $row->id_year,
'sum' => 0,
'debit' => 0,
'credit'=> 0,
'lines' => [],
];
}
$row->date = \DateTime::createFromFormat('Y-m-d', $row->date);
$account->sum += ($row->credit - $row->debit);
$account->debit += $row->debit;
$account->credit += $row->credit;
$debit += $row->debit;
$credit += $row->credit;
$row->running_sum = $account->sum;
$account->lines[] = $row;
}
if (null === $account) {
return;
}
$account->all_debit = $debit;
$account->all_credit = $credit;
yield $account;
}
static public function getJournal(array $criterias): \Generator
{
$where = self::getWhereClause($criterias);
$sql = sprintf('SELECT t.id_year, l.id_account, l.debit, l.credit, t.id, t.date, t.reference, l.reference AS line_reference, t.label, l.label AS line_label FROM acc_transactions t
INNER JOIN acc_transactions_lines l ON l.id_transaction = t.id
WHERE %s ORDER BY t.date, t.id;', $where);
$transaction = null;
$accounts = null;
$db = DB::getInstance();
foreach ($db->iterate($sql) as $row) {
if (null === $accounts) {
$accounts = $db->getGrouped('SELECT id, code, label FROM acc_accounts WHERE id_chart = (SELECT id_chart FROM acc_years WHERE id = ?);', $row->id_year);
}
if (null !== $transaction && $transaction->id != $row->id) {
yield $transaction;
$transaction = null;
}
if (null === $transaction) {
$transaction = (object) [
'id' => $row->id,
'label' => $row->label,
'date' => \DateTime::createFromFormat('Y-m-d', $row->date),
'reference' => $row->reference,
'lines' => [],
];
}
if (!isset($accounts[$row->id_account])) {
throw new \LogicException(sprintf('Account #%s not found', $row->id_account));
}
$transaction->lines[] = (object) [
'account_label' => $accounts[$row->id_account]->label,
'account_code' => $accounts[$row->id_account]->code,
'label' => $row->line_label,
'reference' => $row->line_reference,
'id_account' => $row->id_account,
'credit' => $row->credit,
'debit' => $row->debit,
'id_year' => $row->id_year,
];
}
if (null === $transaction) {
return;
}
yield $transaction;
}
static public function getStatement(array $criterias): array
{
$revenue = Reports::getClosingSumsWithAccounts($criterias + ['position' => Account::REVENUE]);
$expense = Reports::getClosingSumsWithAccounts($criterias + ['position' => Account::EXPENSE], null, true);
$get_sum = function (array $in): int {
$sum = 0;
foreach ($in as $row) {
$sum += $row->sum;
}
return abs($sum);
};
$revenue_sum = $get_sum($revenue);
$expense_sum = $get_sum($expense);
$result = $revenue_sum - $expense_sum;
return compact('revenue', 'expense', 'revenue_sum', 'expense_sum', 'result');
}
}