Overview
Comment:Add expense and revenues in projects view
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk | stable
Files: files | file ages | folders
SHA1: d1dbcfaa8d7b47c517b47c8e67b1cfc4d44318e4
User & Date: bohwaz on 2020-12-14 13:50:14
Other Links: manifest | tags
Context
2020-12-14
21:10
Fix error message when importing invalid accounts check-in: efcf13f264 user: bohwaz tags: trunk, stable
13:50
Add expense and revenues in projects view check-in: d1dbcfaa8d user: bohwaz tags: trunk, stable
2020-12-13
21:06
Return correct form error on formula syntax error check-in: ac91c51fec user: bohwaz tags: trunk, stable
Changes

Modified src/include/lib/Garradin/Accounting/Reports.php from [0a5171425b] to [1c0cdfccbb].

66
67
68
69
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
110
111
112
113
114
115
116
117
118
119
120




121
122
123
124
125
126
127

128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
	 * 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']) : '';







|
>
>
>
>
>
>
>
>
















|


>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>





|
<
<
<
<
<
<
<










<
<
<


>
>
>
>




|
|
|
>






|
|
<
<
<
<
<
<







66
67
68
69
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
110
111
112
113
114
115
116
117
118
119
120
121
122
123







124
125
126
127
128
129
130
131
132
133



134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155






156
157
158
159
160
161
162
	 * 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,
			(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,
			];

			foreach ($sums as $s) {
				$out->{$s} = $current->{$s};
			}

			return $out;
		};

		foreach (DB::getInstance()->iterate($sql) as $row) {
			$id = $by_year ? $row->id_year : $row->id_account;

			if (null !== $current && $current->id !== $id) {
				$current->items[] = $total($current, $by_year);








				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,



					'items' => []
				];

				foreach ($sums as $s) {
					$current->$s = 0;
				}
			}

			$row->label = !$by_year ? $row->year_label : $row->account_label;
			$current->items[] = $row;

			foreach ($sums as $s) {
				$current->$s += $row->$s;
			}
		}

		if ($current === null) {
			return;
		}

		$current->items[] = $total($current, $by_year);







		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']) : '';

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

33
34
35
36
37
38
39


40
41
42
43
44
45
46


	<table class="list projects">
		<thead>
			<tr>
				<td>Année</td>
				<td></td>


				<td class="money">Débits</td>
				<td class="money">Crédits</td>
				<td class="money">Solde</td>
			</tr>
		</thead>
		{foreach from=$list item="parent"}
			<tbody>







>
>







33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48


	<table class="list projects">
		<thead>
			<tr>
				<td>Année</td>
				<td></td>
				<td class="money">Charges</td>
				<td class="money">Produits</td>
				<td class="money">Débits</td>
				<td class="money">Crédits</td>
				<td class="money">Solde</td>
			</tr>
		</thead>
		{foreach from=$list item="parent"}
			<tbody>
59
60
61
62
63
64
65


66
67
68
69
70
71
72
73
74
						| <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.debit|raw|html_money}</td>
					<td class="money">{$item.credit|raw|html_money}</td>
					<td class="money">{$item.sum|raw|html_money:false}</td>
				</tr>
			{/foreach}
			</tbody>
		{/foreach}
	</table>








>
>
|
|







61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
						| <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}
	</table>