source: trunk/Modules/Finance/Trade.php

Last change on this file was 968, checked in by chronos, 9 months ago
  • Modified: Improved content of email with finance account state.
  • Added: Customer IS action to generate finance email into email queue.
File size: 27.9 KB
Line 
1<?php
2
3class PageFinanceTaxFiling extends Page
4{
5 public int $StartEvidence = 0;
6
7 function __construct(System $System)
8 {
9 parent::__construct($System);
10 $this->Title = 'Daňová evidence';
11 $this->ParentClass = 'PageFinance';
12 }
13
14 function GetTimePeriodBalance(int $StartTime, int $EndTime): array
15 {
16 $Balance = array();
17 $DbResult = $this->Database->query('SELECT SUM(`Value`) FROM `FinanceOperation` '.
18 'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id` = `FinanceOperation`.`Group` '.
19 'WHERE (`Time` < "'.TimeToMysqlDateTime($StartTime).'") AND (`Time` >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (`Taxable` = 1) AND (`FinanceOperationGroup`.`ValueSign` = 1)');
20 $Row = $DbResult->fetch_array();
21 $Balance['Income']['Start'] = $Row[0] + 0;
22 $DbResult = $this->Database->query('SELECT SUM(Value) FROM FinanceOperation '.
23 'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id` = `FinanceOperation`.`Group` '.
24 'WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Taxable = 1) AND (`FinanceOperationGroup`.`ValueSign` = 1)');
25 $Row = $DbResult->fetch_array();
26 $Balance['Income']['End'] = $Row[0] + 0;
27
28 $DbResult = $this->Database->query('SELECT SUM(Value*ValueSign) FROM FinanceOperation '.
29 'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id` = `FinanceOperation`.`Group` '.
30 'WHERE (`Time` < "'.TimeToMysqlDateTime($StartTime).'") AND (`Time` >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (`Taxable` = 1) AND (`FinanceOperationGroup`.`ValueSign` = -1)');
31 $Row = $DbResult->fetch_array();
32 $Balance['Spend']['Start'] = $Row[0] + 0;
33 $DbResult = $this->Database->query('SELECT SUM(Value*ValueSign) FROM FinanceOperation '.
34 'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id` = `FinanceOperation`.`Group` '.
35 'WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Taxable = 1) AND (`FinanceOperationGroup`.`ValueSign` = -1)');
36 $Row = $DbResult->fetch_array();
37 $Balance['Spend']['End'] = $Row[0] + 0;
38
39 $DbResult = $this->Database->query('SELECT SUM(Value*ValueSign) FROM FinanceInvoice '.
40 'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `FinanceInvoice`.`Group` '.
41 'WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (`FinanceInvoiceGroup`.`ValueSign` = 1)');
42 $Row = $DbResult->fetch_array();
43 $Balance['Claims']['Start'] = $Row[0] + 0;
44 $DbResult = $this->Database->query('SELECT SUM(Value*ValueSign) FROM FinanceInvoice '.
45 'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `FinanceInvoice`.`Group` '.
46 'WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (`FinanceInvoiceGroup`.`ValueSign` = 1)');
47 $Row = $DbResult->fetch_array();
48 $Balance['Claims']['End'] = $Row[0] + 0;
49
50 $DbResult = $this->Database->query('SELECT SUM(Value*ValueSign) FROM FinanceInvoice '.
51 'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `FinanceInvoice`.`Group` '.
52 'WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (`FinanceInvoiceGroup`.`ValueSign` = -1)');
53 $Row = $DbResult->fetch_array();
54 $Balance['Liabilities']['Start'] = $Row[0] + 0;
55 $DbResult = $this->Database->query('SELECT SUM(Value*ValueSign) FROM FinanceInvoice '.
56 'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `FinanceInvoice`.`Group` '.
57 'WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (`FinanceInvoiceGroup`.`ValueSign` = -1)');
58 $Row = $DbResult->fetch_array();
59 $Balance['Liabilities']['End'] = $Row[0] + 0;
60
61 // SmallAssets
62 $DbResult = $this->Database->query('SELECT SUM(SellPrice) FROM StockSerialNumber WHERE (TimeEnlistment < "'.TimeToMysqlDateTime($StartTime).'") AND (TimeEnlistment != 0) AND ((TimeElimination > "'.TimeToMysqlDateTime($StartTime).'") OR (TimeElimination IS NULL))');
63 //$Output .= $Database->error;
64 $Row = $DbResult->fetch_array();
65 $Balance['SmallAssets']['Start'] = $Row[0] + 0;
66 $DbResult = $this->Database->query('SELECT SUM(SellPrice) FROM StockSerialNumber WHERE (TimeEnlistment < "'.TimeToMysqlDateTime($EndTime).'") AND (TimeEnlistment != 0) AND ((TimeElimination > "'.TimeToMysqlDateTime($EndTime).'") OR (TimeElimination IS NULL))');
67 $Row = $DbResult->fetch_array();
68 $Balance['SmallAssets']['End'] = $Row[0] + 0;
69 return $Balance;
70 }
71
72 function ShowAnnualBalance(): string
73 {
74 $Output = '<table style="font-size: smaller;" class="WideTable">';
75 $Output .= '<tr><th colspan="3">Účetní období</th><th colspan="5">Na konci</th><th colspan="6">Během</th></tr>'.
76 '<tr><th>Rok</th><th>Od</th><th>Do</th><th>Příjmy</th><th>Výdaje</th><th>Pohledávky</th><th>Závazky</th><th>Drobný majetek</th><th>Příjmy</th><th>Výdaje</th><th>Pohledávky</th><th>Závazky</th><th>Drobný majetek</th><th>Základ daně</th></tr>';
77
78 $this->StartEvidence = mktime(0, 0, 0, 12, 1, 2007);
79 $StartYear = date('Y', $this->StartEvidence);
80 $EndYear = date('Y', time());
81 for ($Year = $StartYear; $Year <= $EndYear; $Year++)
82 {
83 $EndTime = mktime(0, 0, 0, 12, 31, $Year);
84 //$Year = date('Y', $EndTime);
85 $StartTime = mktime(0, 0, 0, 1, 1, $Year);
86 if ($StartTime < $this->StartEvidence) $StartTime = $this->StartEvidence;
87
88 $Balance = $this->GetTimePeriodBalance($StartTime, $EndTime);
89
90 $Output .= '<tr><td>'.$Year.'</td><td>'.date(HumanDate(TimeToMysqlDateTime($StartTime))).'</td><td>'.date(HumanDate(TimeToMysqlDateTime($EndTime))).'</td>';
91 $Output .= '<td>'.round($Balance['Income']['End']).'</td>';
92 $Output .= '<td>'.round($Balance['Spend']['End']).'</td>';
93 $Output .= '<td>'.round($Balance['Claims']['End']).'</td>';
94 $Output .= '<td>'.round($Balance['Liabilities']['End']).'</td>';
95 $Output .= '<td>'.round($Balance['SmallAssets']['End']).'</td>';
96
97 $Output .= '<td>'.round($Balance['Income']['End'] - $Balance['Income']['Start']).'</td>';
98 $Output .= '<td>'.round($Balance['Spend']['End'] - $Balance['Spend']['Start']).'</td>';
99 $Output .= '<td>'.round($Balance['Claims']['End'] - $Balance['Claims']['Start']).'</td>';
100 $Output .= '<td>'.round($Balance['Liabilities']['End'] - $Balance['Liabilities']['Start']).'</td>';
101 $Output .= '<td>'.round($Balance['SmallAssets']['End'] - $Balance['SmallAssets']['Start']).'</td>';
102 $Output .= '<td>'.round(($Balance['Income']['End'] - $Balance['Income']['Start']) - ($Balance['Spend']['End'] - $Balance['Spend']['Start'])).'</td></tr>';
103 }
104 $Output .= '</table>';
105 return $Output;
106 }
107
108 function ShowMonthlyBalance(): string
109 {
110 $Output = '<table class="WideTable" style="font-size: smaller;">';
111 $Output .= '<tr><th colspan="4">Účetní období</th><th colspan="5">Na konci</th><th colspan="6">Během</th></tr>'.
112 '<tr><th>Rok</th><th>Měsíc</th></th><th>Od</th><th>Do</th><th>Příjmy</th><th>Výdaje</th><th>Pohledávky</th><th>Závazky</th><th>Drobný majetek</th><th>Příjmy</th><th>Výdaje</th><th>Pohledávky</th><th>Závazky</th><th>Drobný majetek</th><th>Základ daně</th></tr>';
113
114 $this->StartEvidence = mktime(0, 0, 0, 12, 1, 2007);
115 $StartYear = date('Y', $this->StartEvidence);
116 $EndYear = date('Y', time());
117 for ($Year = $StartYear; $Year <= $EndYear; $Year++)
118 {
119 for ($Month = 1; $Month <= 12; $Month++)
120 {
121 $EndTime = mktime(0, 0, 0, $Month, 31, $Year);
122 //$Year = date('Y', $EndTime);
123 $StartTime = mktime(0, 0, 0, $Month, 1, $Year);
124 if (($StartTime < time()) and ($EndTime > $this->StartEvidence))
125 {
126 if ($StartTime < $this->StartEvidence) $StartTime = $this->StartEvidence;
127
128 $Balance = $this->GetTimePeriodBalance($StartTime, $EndTime);
129
130 $Output .= '<tr><td>'.$Year.'</td><td>'.$Month.'</td><td>'.date(HumanDate(TimeToMysqlDateTime($StartTime))).'</td><td>'.date(HumanDate(TimeToMysqlDateTime($EndTime))).'</td>';
131 $Output .= '<td>'.round($Balance['Income']['End']).'</td>';
132 $Output .= '<td>'.round($Balance['Spend']['End']).'</td>';
133 $Output .= '<td>'.round($Balance['Claims']['End']).'</td>';
134 $Output .= '<td>'.round($Balance['Liabilities']['End']).'</td>';
135 $Output .= '<td>'.round($Balance['SmallAssets']['End']).'</td>';
136
137 $Output .= '<td>'.round($Balance['Income']['End'] - $Balance['Income']['Start']).'</td>';
138 $Output .= '<td>'.round($Balance['Spend']['End'] - $Balance['Spend']['Start']).'</td>';
139 $Output .= '<td>'.round($Balance['Claims']['End'] - $Balance['Claims']['Start']).'</td>';
140 $Output .= '<td>'.round($Balance['Liabilities']['End'] - $Balance['Liabilities']['Start']).'</td>';
141 $Output .= '<td>'.round($Balance['SmallAssets']['End'] - $Balance['SmallAssets']['Start']).'</td>';
142 $Output .= '<td>'.round(($Balance['Income']['End'] - $Balance['Income']['Start']) - ($Balance['Spend']['End'] - $Balance['Spend']['Start'])).'</td></tr>';
143 }
144 }
145 }
146 $Output .= '</table>';
147 return $Output;
148 }
149
150 function ShowIncomes(): string
151 {
152 $Table = array('Ne', 'Ano');
153 $DbResult = $this->Database->select('FinanceYear', '*', 'Id='.$_GET['year']);
154 $Year = $DbResult->fetch_assoc();
155
156 $Total = 0;
157 $TotalTax = array(0, 0);
158 $TotalCash = array(0, 0);
159 $Output = '<strong>Příjmy za rok '.$Year['Year'].'</strong>';
160 $Output .= '<table style="font-size: smaller;" class="WideTable">';
161 $Output .= '<tr><th>Čas</th><th>Kód</th><th>Subjekt</th><th>Text</th><th>Hodnota [Kč]</th><th>Daňový</th><th>Hotovost</th></tr>';
162 $DbResult = $this->Database->query('SELECT `FinanceOperation`.*, `DocumentLineCode`.`Name` AS `BillName`, `Subject`.`Name` AS `Name` FROM `FinanceOperation` '.
163 'LEFT JOIN `Subject` ON `Subject`.`Id` = `FinanceOperation`.`Subject` '.
164 'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id` = `FinanceOperation`.`Group` '.
165 'LEFT JOIN `DocumentLineCode` ON `DocumentLineCode`.`Id` = `FinanceOperation`.`BillCode` '.
166 'WHERE (`ValueSign` = 1) AND (`FinanceOperation`.`Time` >= "'.$Year['DateStart'].'") '.
167 'AND (`FinanceOperation`.`Time` <= "'.$Year['DateEnd'].'") ORDER BY `Time`');
168 while ($Row = $DbResult->fetch_array())
169 {
170 $Row['Time'] = explode(' ', $Row['Time']);
171 $Row['Time'] = $Row['Time'][0];
172 $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.$Row['BillName'].
173 '</td><td>'.$Row['Name'].'</td><td>'.$Row['Text'].'</td><td>'.$Row['Value'].
174 '</td><td>'.$Table[$Row['Taxable']].'</td><td>'.$Table[$Row['Cash']].'</td></tr>';
175 $Total += $Row['Value'];
176 $TotalTax[$Row['Taxable']] += $Row['Value'];
177 $TotalCash[$Row['Cash']] += $Row['Value'];
178 }
179 $Output .= '</table><br/>';
180 $Output .= '<strong>Souhrn</strong><br/>';
181 $Output .= 'Celkem: '.$Total.' Kč<br/>';
182 $Output .= ' Daňové: '.$TotalTax[1].' Kč<br/>';
183 $Output .= ' Nedaňové: '.$TotalTax[0].' Kč<br/>';
184 $Output .= ' Hotovostní: '.$TotalCash[1].' Kč<br/>';
185 $Output .= ' Bezhotovostní: '.$TotalCash[0].' Kč<br/>';
186 return $Output;
187 }
188
189 function ShowExpenses(): string
190 {
191 $Table = array('Ne', 'Ano');
192 $DbResult = $this->Database->select('FinanceYear', '*', 'Id='.$_GET['year']);
193 $Year = $DbResult->fetch_assoc();
194
195 $Total = 0;
196 $TotalTax = array(0, 0);
197 $TotalCash = array(0, 0);
198 $Output = '<strong>Výdaje za rok '.$Year['Year'].'</strong>';
199 $Output .= '<table style="font-size: smaller;" class="WideTable">';
200 $Output .= '<tr><th>Čas</th><th>Kód</th><th>Subjekt</th><th>Text</th><th>Hodnota [Kč]</th><th>Daňový</th><th>Hotovost</th></tr>';
201 $DbResult = $this->Database->query('SELECT `FinanceOperation`.*, `DocumentLineCode`.`Name` AS `BillName`, '.
202 '`Subject`.`Name` AS `SubjectName`, `FinanceOperationGroup`.`ValueSign` AS `ValueSign` FROM `FinanceOperation` '.
203 'LEFT JOIN `Subject` ON `Subject`.`Id` = `FinanceOperation`.`Subject` '.
204 'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id` = `FinanceOperation`.`Group` '.
205 'LEFT JOIN `DocumentLineCode` ON `DocumentLineCode`.`Id` = `FinanceOperation`.`BillCode` '.
206 'WHERE (`FinanceOperationGroup`.`ValueSign` = -1) AND (`FinanceOperation`.`Time` >= "'.$Year['DateStart'].'") '.
207 'AND (`FinanceOperation`.`Time` <= "'.$Year['DateEnd'].'") ORDER BY `Time`');
208 while ($Row = $DbResult->fetch_array())
209 {
210 $Row['Time'] = explode(' ', $Row['Time']);
211 $Row['Time'] = $Row['Time'][0];
212 $Row['Value'] = $Row['Value'] * $Row['ValueSign'];
213 $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.$Row['BillName'].
214 '</td><td>'.$Row['SubjectName'].'</td><td>'.$Row['Text'].'</td><td>'.$Row['Value'].
215 '</td><td>'.$Table[$Row['Taxable']].'</td><td>'.$Table[$Row['Cash']].'</td></tr>';
216 $Total += $Row['Value'];
217 $TotalTax[$Row['Taxable']] += $Row['Value'];
218 $TotalCash[$Row['Cash']] += $Row['Value'];
219 }
220 $Output .= '</table><br/>';
221 $Output .= '<strong>Souhrn</strong><br/>';
222 $Output .= 'Celkem: '.$Total.' Kč<br/>';
223 $Output .= ' Daňové: '.$TotalTax[1].' Kč<br/>';
224 $Output .= ' Nedaňové: '.$TotalTax[0].' Kč<br/>';
225 $Output .= ' Hotovostní: '.$TotalCash[1].' Kč<br/>';
226 $Output .= ' Bezhotovostní: '.$TotalCash[0].' Kč<br/>';
227 return $Output;
228 }
229
230 function ShowClaims(): string
231 {
232 $Table = array('Ne', 'Ano');
233 $DbResult = $this->Database->select('FinanceYear', '*', 'Id='.$_GET['year']);
234 $Year = $DbResult->fetch_assoc();
235
236 $Total = 0;
237 $Output = '<strong>Pohledávky za rok '.$Year['Year'].'</strong>';
238 $Output .= '<table style="font-size: smaller;" class="WideTable">';
239 $Output .= '<tr><th>Čas vystavení</th><th>Kód</th><th>Subjekt</th><th>Text</th><th>Hodnota [Kč]</th></tr>';
240 $DbResult = $this->Database->query('SELECT `FinanceInvoice`.*, '.
241 '`DocumentLineCode`.`Name` AS `BillName`, `Subject`.`Name` AS `SubjectName`, '.
242 '(SELECT GROUP_CONCAT(`Description` SEPARATOR ",") '.
243 'FROM `FinanceInvoiceItem` WHERE `FinanceInvoiceItem`.`FinanceInvoice` = `FinanceInvoice`.`Id`) AS `Text` FROM `FinanceInvoice`'.
244 'LEFT JOIN `Subject` ON `Subject`.`Id` = `FinanceInvoice`.`Subject` '.
245 'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `FinanceInvoice`.`Group` '.
246 'LEFT JOIN `DocumentLineCode` ON `DocumentLineCode`.`Id` = `FinanceInvoice`.`BillCode` '.
247 'WHERE (`FinanceInvoiceGroup`.`ValueSign` = 1) AND (`FinanceInvoice`.`Time` >= "'.$Year['DateStart'].
248 '") AND (`FinanceInvoice`.`Time` <= "'.$Year['DateEnd'].'") ORDER BY `Time`');
249 while ($Row = $DbResult->fetch_array())
250 {
251 if ($Row['TimePayment'] == '0000-00-00 00:00:00') $Row['TimePayment'] = '&nbsp;';
252 $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.$Row['BillName'].
253 '</td><td>'.$Row['SubjectName'].'</td><td>'.$Row['Text'].'</td><td>'.$Row['Value'].'</td></tr>';
254 $Total += $Row['Value'];
255 }
256 $Output .= '</table><br/>';
257 $Output .= 'Celkem: '.$Total.' Kč<br/>';
258 return $Output;
259 }
260
261 function ShowLiabilities(): string
262 {
263 $Table = array('Ne', 'Ano');
264 $DbResult = $this->Database->select('FinanceYear', '*', 'Id='.$_GET['year']);
265 $Year = $DbResult->fetch_assoc();
266
267 $Total = 0;
268 $Output = '<strong>Závazky za rok '.$Year['Year'].'</strong>';
269 $Output .= '<table style="font-size: smaller;" class="WideTable">';
270 $Output .= '<tr><th>Čas vystavení</th><th>Kód</th><th>Subjekt</th><th>Text</th><th>Hodnota [Kč]</th></tr>';
271 $DbResult = $this->Database->query('SELECT *, `DocumentLineCode`.`Name` AS `BillName`, `Subject`.`Name` AS `SubjectName`, '.
272 '(SELECT GROUP_CONCAT(Description SEPARATOR ",") '.
273 'FROM `FinanceInvoiceItem` WHERE `FinanceInvoiceItem`.`FinanceInvoice` = `FinanceInvoice`.`Id`) AS `Text` '.
274 'FROM `FinanceInvoice` '.
275 'LEFT JOIN `Subject` ON `Subject`.`Id` = `FinanceInvoice`.`Subject` '.
276 'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `FinanceInvoice`.`Group` '.
277 'LEFT JOIN `DocumentLineCode` ON `DocumentLineCode`.`Id` = `FinanceInvoice`.`BillCode` '.
278 'WHERE (`FinanceInvoiceGroup`.`ValueSign` = -1) AND (`FinanceInvoice`.`Time` >= "'.$Year['DateStart'].
279 '") AND (FinanceInvoice.Time <= "'.$Year['DateEnd'].'") ORDER BY Time');
280 while ($Row = $DbResult->fetch_array())
281 {
282 if ($Row['TimePayment'] == '0000-00-00 00:00:00') $Row['TimePayment'] = '&nbsp;';
283 $Row['Value'] = $Row['Value'] * $Row['ValueSign'];
284 $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.$Row['BillName'].
285 '</td><td>'.$Row['SubjectName'].'</td><td>'.$Row['Text'].'</td><td>'.$Row['Value'].'</td></tr>';
286 $Total += $Row['Value'];
287 }
288 $Output .= '</table><br/>';
289 $Output .= 'Celkem: '.$Total.' Kč<br/>';
290 return $Output;
291 }
292
293 function ShowSubjectList(): string
294 {
295 $Output = '<strong>Seznam subjektů</strong>';
296 $Output .= '<table style="font-size: smaller;" class="WideTable">';
297 $Output .= '<tr><th>Jméno</th><th>Závazky [Kč]</th><th>Pohledávky [Kč]</th><th>Příjmy [Kč]</th><th>Výdaje [Kč]</th><th>Stav účtu</th><th>Starý systém</th></tr>';
298 $DbResult = $this->Database->query('SELECT Id, Name, 0 AS Cash, '.
299 '(SELECT SUM(T1.Value) FROM FinanceInvoice AS T1 '.
300 'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `T1`.`Group` '.
301 'WHERE (T1.Subject = Subject.Id) AND (`FinanceInvoiceGroup`.ValueSign = 1)) AS `Claims`, '.
302 '(SELECT SUM(T2.Value) FROM FinanceInvoice AS T2 '.
303 'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `T2`.`Group` '.
304 'WHERE (T2.Subject = Subject.Id) AND (`FinanceInvoiceGroup`.`ValueSign` = 1) AND (TimePayment IS NULL)) AS `OpenedClaims`, '.
305 '(SELECT -SUM(T3.Value) FROM FinanceInvoice AS T3 '.
306 'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `T3`.`Group` '.
307 'WHERE (T3.Subject = Subject.Id) AND (`FinanceInvoiceGroup`.`ValueSign` = -1)) AS `Liabilities`, '.
308 '(SELECT -SUM(T4.Value) FROM FinanceInvoice AS T4 '.
309 'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `T4`.`Group` '.
310 'WHERE (T4.Subject = Subject.Id) AND (`FinanceInvoiceGroup`.`ValueSign` = -1) AND (`TimePayment` IS NULL)) AS `OpenedLiabilities`, '.
311 '(SELECT SUM(T5.Value) FROM FinanceOperation AS T5 '.
312 'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id` = `T5`.`Group` '.
313 'WHERE (T5.Subject = Subject.Id) AND (`FinanceOperationGroup`.`ValueSign` = 1)) AS `Gains`, '.
314 '(SELECT SUM(T6.Value) FROM FinanceOperation AS T6 '.
315 'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id` = `T6`.`Group` '.
316 'WHERE (T6.Subject = Subject.Id) AND (`FinanceOperationGroup`.`ValueSign` = -1)) AS `Spends` '.
317 'FROM Subject ORDER BY Name');
318 while ($Row = $DbResult->fetch_assoc())
319 {
320 $Output .= '<tr><td style="text-align: left;"><a href="?table=SubjectAccount&Id='.$Row['Id'].'">'.$Row['Name'].'</a></td><td>'.$Row['Liabilities'].' / '.$Row['OpenedLiabilities'].'</td><td>'.$Row['Claims'].' / '.$Row['OpenedClaims'].'</td><td>'.$Row['Gains'].'</td><td>'.$Row['Spends'].'</td><td>'.($Row['Gains'] - $Row['Spends'] - $Row['Claims'] + $Row['Liabilities']).'</td><td>'.$Row['Cash'].'</td></tr>';
321 }
322 $Output .= '</table>';
323 return $Output;
324 }
325
326 function ShowSmallAssets(): string
327 {
328 $Output = '<strong>Drobný majetek</strong>';
329 $Output .= '<table style="font-size: smaller;" border="1" cellspacing="0" cellpadding="3">';
330 $Output .= '<tr><th>Název</th><th>Hodnota [Kč]</th><th>Datum zakoupení</th><th>Datum vyřezení</th></tr>';
331 $DbResult = $this->Database->query('SELECT Product.Name AS Name, StockSerialNumber.SellPrice AS Price, StockSerialNumber.TimeEnlistment AS TimeEnlistment, StockSerialNumber.TimeElimination AS TimeElimination '.
332 'FROM StockSerialNumber JOIN Product ON Product.Id = StockSerialNumber.Product '.
333 'WHERE (TimeElimination IS NOT NULL)');
334 while ($Row = $DbResult->fetch_array())
335 {
336 $Output .= '<tr><td>'.$Row['Name'].'</td><td>'.$Row['Price'].'</td><td>'.$Row['TimeEnlistment'].'</td><td>'.$Row['TimeElimination'].'</td></tr>';
337 }
338 $Output .= '</table>';
339 return $Output;
340 }
341
342 function ShowSubjectAccount(): string
343 {
344 $Finance = &ModuleFinance::Cast($this->System->GetModule('Finance'))->Finance;
345
346 $Output = '<table style="width: 100%"><tr><td style="vertical-align: top;">';
347 $Output .= '<strong>Výpis příjmů/výdajů</strong>';
348 $Output .= '<table style="font-size: smaller;" border="1" cellspacing="0" cellpadding="3">';
349 $Output .= '<tr><th>Datum</th><th>Název</th><th>Hodnota [Kč]</th><th>Doklad</th></tr>';
350 $DbResult = $this->Database->query('SELECT `FinanceOperation`.*, `DocumentLineCode`.`Name` AS `BillName` '.
351 'FROM `FinanceOperation` '.
352 'LEFT JOIN `DocumentLineCode` ON `DocumentLineCode`.`Id` = `FinanceOperation`.`BillCode` '.
353 'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id` = `FinanceOperation`.`Group` '.
354 'WHERE `Subject`='.$_GET['Id'].' ORDER BY `Time`');
355 while ($Row = $DbResult->fetch_array())
356 {
357 $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.$Row['Text'].
358 '</td><td>'.$Row['Value'].'</td><td>'.$Row['BillName'].'</td></tr>';
359 }
360 $Output .= '</table></td><td style="vertical-align: top;">';
361
362 $Output .= '<strong>Výpis závazků/pohledávek</strong>';
363 $Output .= '<table style="font-size: smaller;" border="1" cellspacing="0" cellpadding="3">';
364 $Output .= '<tr><th>Datum vytvoření</th><th>Datum zaplacení</th><th>Název</th><th>Hodnota [Kč]</th><th>Doklad</th></tr>';
365 $DbResult = $this->Database->query('SELECT `FinanceInvoice`.*, (SELECT GROUP_CONCAT(Description SEPARATOR ",") '.
366 'FROM FinanceInvoiceItem WHERE `FinanceInvoiceItem`.`FinanceInvoice` = `FinanceInvoice`.`Id`) AS `Text`, `DocumentLineCode`.`Name` AS `BillName` '.
367 'FROM `FinanceInvoice` '.
368 'LEFT JOIN `DocumentLineCode` ON `DocumentLineCode`.`Id` = `FinanceInvoice`.`BillCode` '.
369 'WHERE `Subject`='.$_GET['Id'].' ORDER BY `Time`');
370 while ($Row = $DbResult->fetch_array())
371 {
372 $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.HumanDate($Row['TimePayment']).
373 '</td><td>'.$Row['Text'].'</td><td>'.round($Row['Value'], $Finance->Rounding).'</td><td>'.$Row['BillName'].'</td></tr>';
374 }
375 $Output .= '</table></td></tr></table>';
376
377 /*$Output .= '<strong>Výpis záloh</strong>';
378 $Output .= '<table style="font-size: smaller;" border="1" cellspacing="0" cellpadding="3">';
379 $Output .= '<tr><th>Datum vytvoření</th><th>Datum zaplacení</th><th>Název</th><th>Hodnota [Kč]</th></tr>';
380 $DbResult = $this->Database->select('FinanceAdvances', '*', 'Subject='.$_GET['Id']);
381 while ($Row = $DbResult->fetch_array())
382 {
383 $Output .= '<tr><td>'.$Row['Time'].'</td><td>'.$Row['TimePass'].'</td><td>'.$Row['ValueSign'].'</td><td>'.($Row['Value']).'</td><td>'.$Row['CashFlowId'].'</td></tr>';
384 }
385 $Output .= '</table>';*/
386
387 $DbResult = $this->Database->query('SELECT Id, 0 AS Cash, (SELECT SUM(FinanceInvoice.Value * FinanceInvoice.ValueSign) '.
388 'FROM FinanceInvoice WHERE (FinanceInvoice.Subject = Subject.Id) AND '.
389 '(FinanceInvoice.ValueSign = 1)) as Claims, (SELECT SUM(FinanceInvoice.Value * FinanceInvoice.ValueSign) '.
390 'FROM FinanceInvoice WHERE (FinanceInvoice.Subject = Subject.Id) AND ('.
391 'FinanceInvoice.ValueSign = 1) AND (TimePayment IS NULL)) as OpenedClaims, '.
392 '(SELECT SUM(FinanceInvoice.Value * FinanceInvoice.ValueSign) FROM FinanceInvoice '.
393 'WHERE (FinanceInvoice.Subject = Subject.Id) AND (FinanceInvoice.ValueSign = -1)) AS '.
394 'Liabilities, (SELECT SUM(FinanceInvoice.Value) FROM FinanceInvoice '.
395 'WHERE (FinanceInvoice.Subject = Subject.Id) AND (FinanceInvoice.ValueSign = -1) '.
396 'AND (TimePayment IS NULL)) AS OpenedLiabilities, '.
397 '(SELECT SUM(FinanceOperation.Value*FinanceOperation.ValueSign) '.
398 'FROM FinanceOperation WHERE FinanceOperation.Subject = Subject.Id AND FinanceOperation.ValueSign = 1) '.
399 'AS Gains, (SELECT SUM(FinanceOperation.Value*FinanceOperation.ValueSign) FROM FinanceOperation WHERE '.
400 'FinanceOperation.Subject = Subject.Id AND FinanceOperation.ValueSign = -1) as Spends '.
401 'FROM Subject WHERE Id='.$_GET['Id']);
402 $Row = $DbResult->fetch_array();
403 $Output .= 'Stav placení: '.(-$Row['OpenedClaims'] + Abs($Row['OpenedLiabilities']));
404 return $Output;
405 }
406
407 function ShowDefault(): string
408 {
409 $Output = '<strong>Celkové přehledy</strong><br/>';
410 $Output .= '<a href="?table=AnnualBalance">Účetní závěrka</a><br />';
411 $Output .= '<a href="?table=MonthlyBalance">Měsíční přehledy</a><br />';
412 $Output .= '<a href="?table=SubjectList">Seznam subjektů</a><br />';
413 $Output .= '<a href="?table=SmallAssets">Drobný majetek</a><br />';
414 $Output .= '<br/>';
415 $Output .= '<strong>Roční přehledy</strong><br/>';
416 $Output .= $this->ShowFinanceYears();
417 if (array_key_exists('year', $_GET))
418 {
419 $Year = $_GET['year'] * 1;
420 $Output .= '<a href="?table=Incomes&amp;year='.$Year.'">Příjmy</a><br />';
421 $Output .= '<a href="?table=Expenses&amp;year='.$Year.'">Výdaje</a><br />';
422 $Output .= '<a href="?table=Claims&amp;year='.$Year.'">Pohledávky</a><br />';
423 $Output .= '<a href="?table=Liabilities&amp;year='.$Year.'">Závazky</a><br />';
424 $Output .= '<a href="?table=Advantages&amp;year='.$Year.'">Zálohy</a><br />';
425 $Output .= '<a href="?table=YearReport&amp;year='.$Year.'">Souhrný přehled</a><br />';
426 }
427 return $Output;
428 }
429
430 function Show(): string
431 {
432 if (!ModuleUser::Cast($this->System->GetModule('User'))->User->CheckPermission('Finance', 'TradingStatus'))
433 return 'Nemáte oprávnění';
434
435 $Output = '';
436 if (!array_key_exists('table', $_GET)) $_GET['table'] = '';
437 switch ($_GET['table'])
438 {
439 case 'AnnualBalance':
440 $Output = $this->ShowAnnualBalance();
441 break;
442 case 'MonthlyBalance':
443 $Output = $this->ShowMonthlyBalance();
444 break;
445 case 'YearReport':
446 $Output .= $this->ShowIncomes().'<hr>';
447 $Output .= $this->ShowExpenses().'<hr>';
448 $Output .= $this->ShowClaims().'<hr>';
449 $Output .= $this->ShowLiabilities().'<hr>';
450 break;
451 case 'Incomes':
452 $Output = $this->ShowIncomes();
453 break;
454 case 'Expenses':
455 $Output = $this->ShowExpenses();
456 break;
457 case 'Claims':
458 $Output = $this->ShowClaims();
459 break;
460 case 'Liabilities':
461 $Output = $this->ShowLiabilities();
462 break;
463 case 'SubjectList':
464 $Output = $this->ShowSubjectList();
465 break;
466 case 'SmallAssets':
467 $Output = $this->ShowSmallAssets();
468 break;
469 case 'SubjectAccount':
470 $Output = $this->ShowSubjectAccount();
471 break;
472 case 'PrintMonthOperations':
473 $Output .= '<table><tr><th>Datum</th><th>Subjekt</th><td></td></tr></table>';
474 break;
475 case 'PrintMonthOperations':
476 $Output .= 'Výpis operací od '.$_GET['TimeFrom'].' do '.$_GET['TimeTo'];
477 $Output .= '<table><tr><th>Datum</th><th>Subjekt</th><th>Suma [Kč]</th><td></td></tr></table>';
478 break;
479 default:
480 $Output = $this->ShowDefault();
481 }
482 return $Output;
483 }
484
485 function ShowFinanceYears(): string
486 {
487 $Output = 'Roky: ';
488 $DbRows = $this->Database->select('FinanceYear', '*');
489 while ($DbRow = $DbRows->fetch_assoc())
490 $Output .= '<a href="?year='.$DbRow['Id'].'">'.$DbRow['Year'].'</a> ';
491 $Output .= '<br/>';
492 return $Output;
493 }
494
495 function AddMoney($Subject, $Value, $Text, $Time)
496 {
497 $TimeDue = $Time + 15 * 24 * 3600; // 15 dnů
498 }
499
500 function AddInvoice($Subject, $Value, $Text, $Time)
501 {
502 $TimeDue = $Time + 15 * 24 * 3600; // 15 dnů
503 }
504}
Note: See TracBrowser for help on using the repository browser.