Overview
Comment:Fix projects revenus and expenses
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 43b35b623e0fcc8d2e3d15d5d689fb34eeafa490
User & Date: bohwaz on 2020-12-17 18:40:49
Other Links: manifest | tags
Context
2020-12-17
18:42
Remove defaults, they were creating more bugs than resolving anything check-in: ed1b864ad5 user: bohwaz tags: trunk
18:40
Fix projects revenus and expenses check-in: 43b35b623e user: bohwaz tags: trunk
18:21
Try to fix another issue with forms check-in: c151d71bc6 user: bohwaz tags: trunk, stable
Changes

Modified src/include/lib/Garradin/Accounting/Reports.php from [d2cf5396b1] to [0c4c46a2a9].

70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
	{
		$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,
			(SELECT SUM(l2.credit - l2.debit) FROM acc_transactions_lines l2
				INNER JOIN acc_transactions t2 ON t2.id = l2.id_transaction
				INNER JOIN acc_accounts a2 ON a2.id = l2.id_account
				WHERE a2.position = %d AND l2.id_analytical = l.id_analytical) * -1 AS sum_expense,
			(SELECT SUM(l2.credit - l2.debit) FROM acc_transactions_lines l2
				INNER JOIN acc_transactions t2 ON t2.id = l2.id_transaction
				INNER JOIN acc_accounts a2 ON a2.id = l2.id_account
				WHERE a2.position = %d AND l2.id_analytical = l.id_analytical) AS sum_revenue
			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, Account::EXPENSE, Account::REVENUE, $group, $order);

		$current = null;

		static $sums = ['credit', 'debit', 'sum', 'sum_expense', 'sum_revenue'];

		$total = function (\stdClass $current, bool $by_year) use ($sums)
		{
			$out = (object) [
				'label' => 'Total',
				'id_account' => $by_year ? null : $current->id,
				'id_year' => $by_year ? $current->id : null,







|



|




















|







70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
	{
		$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,
			(SELECT SUM(l2.credit - l2.debit) FROM acc_transactions_lines l2
				INNER JOIN acc_transactions t2 ON t2.id = l2.id_transaction
				INNER JOIN acc_accounts a2 ON a2.id = l2.id_account
				WHERE a2.position = %d AND l2.id_analytical = l.id_analytical AND t2.id_year = t.id_year) * -1 AS sum_expense,
			(SELECT SUM(l2.credit - l2.debit) FROM acc_transactions_lines l2
				INNER JOIN acc_transactions t2 ON t2.id = l2.id_transaction
				INNER JOIN acc_accounts a2 ON a2.id = l2.id_account
				WHERE a2.position = %d AND l2.id_analytical = l.id_analytical AND t2.id_year = t.id_year) AS sum_revenue
			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, Account::EXPENSE, Account::REVENUE, $group, $order);

		$current = null;

		static $sums = ['credit', 'debit', 'sum'];

		$total = function (\stdClass $current, bool $by_year) use ($sums)
		{
			$out = (object) [
				'label' => 'Total',
				'id_account' => $by_year ? null : $current->id,
				'id_year' => $by_year ? $current->id : null,

Modified src/templates/acc/reports/projects.tpl from [8e3cf20aac] to [c1c139c152].

61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
						| <a href="{$admin_url}acc/reports/trial_balance.php?analytical={$item.id_account}&year={$item.id_year}">Balance générale</a>
						| <a href="{$admin_url}acc/reports/journal.php?analytical={$item.id_account}&year={$item.id_year}">Journal général</a>
						| <a href="{$admin_url}acc/reports/ledger.php?analytical={$item.id_account}&year={$item.id_year}">Grand livre</a>
						| <a href="{$admin_url}acc/reports/statement.php?analytical={$item.id_account}&year={$item.id_year}">Compte de résultat</a>
						| <a href="{$admin_url}acc/reports/balance_sheet.php?analytical={$item.id_account}&year={$item.id_year}">Bilan</a>
					</span>
					</td>
					<td class="money">{$item.sum_expense|raw|html_money:false}</td>
					<td class="money">{$item.sum_revenue|raw|html_money:false}</td>
					<td class="money">{$item.debit|raw|html_money:false}</td>
					<td class="money">{$item.credit|raw|html_money:false}</td>
					<td class="money">{$item.sum|raw|html_money:false}</td>
				</tr>
			{/foreach}
			</tbody>
		{/foreach}







|
|







61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
						| <a href="{$admin_url}acc/reports/trial_balance.php?analytical={$item.id_account}&year={$item.id_year}">Balance générale</a>
						| <a href="{$admin_url}acc/reports/journal.php?analytical={$item.id_account}&year={$item.id_year}">Journal général</a>
						| <a href="{$admin_url}acc/reports/ledger.php?analytical={$item.id_account}&year={$item.id_year}">Grand livre</a>
						| <a href="{$admin_url}acc/reports/statement.php?analytical={$item.id_account}&year={$item.id_year}">Compte de résultat</a>
						| <a href="{$admin_url}acc/reports/balance_sheet.php?analytical={$item.id_account}&year={$item.id_year}">Bilan</a>
					</span>
					</td>
					<td class="money">{$item.sum_expense|raw|html_money}</td>
					<td class="money">{$item.sum_revenue|raw|html_money}</td>
					<td class="money">{$item.debit|raw|html_money:false}</td>
					<td class="money">{$item.credit|raw|html_money:false}</td>
					<td class="money">{$item.sum|raw|html_money:false}</td>
				</tr>
			{/foreach}
			</tbody>
		{/foreach}