source: trunk/Modules/Finance/Trade.php@ 847

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