[70] | 1 | <?php
|
---|
| 2 |
|
---|
[521] | 3 | class PageFinanceTaxFiling extends Page
|
---|
[70] | 4 | {
|
---|
[888] | 5 | public int $StartEvidence = 0;
|
---|
[70] | 6 |
|
---|
[887] | 7 | function __construct(System $System)
|
---|
| 8 | {
|
---|
| 9 | parent::__construct($System);
|
---|
[912] | 10 | $this->Title = 'Daňová evidence';
|
---|
[887] | 11 | $this->ParentClass = 'PageFinance';
|
---|
| 12 | }
|
---|
| 13 |
|
---|
[888] | 14 | function GetTimePeriodBalance(int $StartTime, int $EndTime): array
|
---|
[165] | 15 | {
|
---|
| 16 | $Balance = array();
|
---|
[748] | 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)');
|
---|
[165] | 20 | $Row = $DbResult->fetch_array();
|
---|
| 21 | $Balance['Income']['Start'] = $Row[0] + 0;
|
---|
[748] | 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)');
|
---|
[165] | 25 | $Row = $DbResult->fetch_array();
|
---|
| 26 | $Balance['Income']['End'] = $Row[0] + 0;
|
---|
[70] | 27 |
|
---|
[748] | 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)');
|
---|
[165] | 31 | $Row = $DbResult->fetch_array();
|
---|
| 32 | $Balance['Spend']['Start'] = $Row[0] + 0;
|
---|
[748] | 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)');
|
---|
[165] | 36 | $Row = $DbResult->fetch_array();
|
---|
| 37 | $Balance['Spend']['End'] = $Row[0] + 0;
|
---|
| 38 |
|
---|
[748] | 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)');
|
---|
[165] | 42 | $Row = $DbResult->fetch_array();
|
---|
| 43 | $Balance['Claims']['Start'] = $Row[0] + 0;
|
---|
[748] | 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)');
|
---|
[165] | 47 | $Row = $DbResult->fetch_array();
|
---|
| 48 | $Balance['Claims']['End'] = $Row[0] + 0;
|
---|
| 49 |
|
---|
[748] | 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)');
|
---|
[165] | 53 | $Row = $DbResult->fetch_array();
|
---|
| 54 | $Balance['Liabilities']['Start'] = $Row[0] + 0;
|
---|
[748] | 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)');
|
---|
[165] | 58 | $Row = $DbResult->fetch_array();
|
---|
| 59 | $Balance['Liabilities']['End'] = $Row[0] + 0;
|
---|
| 60 |
|
---|
| 61 | // SmallAssets
|
---|
[710] | 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))');
|
---|
[165] | 63 | //$Output .= $Database->error;
|
---|
| 64 | $Row = $DbResult->fetch_array();
|
---|
| 65 | $Balance['SmallAssets']['Start'] = $Row[0] + 0;
|
---|
[710] | 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))');
|
---|
[165] | 67 | $Row = $DbResult->fetch_array();
|
---|
| 68 | $Balance['SmallAssets']['End'] = $Row[0] + 0;
|
---|
[874] | 69 | return $Balance;
|
---|
[738] | 70 | }
|
---|
[165] | 71 |
|
---|
[888] | 72 | function ShowAnnualBalance(): string
|
---|
[152] | 73 | {
|
---|
[847] | 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>';
|
---|
[70] | 77 |
|
---|
[847] | 78 | $this->StartEvidence = mktime(0, 0, 0, 12, 1, 2007);
|
---|
| 79 | $StartYear = date('Y', $this->StartEvidence);
|
---|
| 80 | $EndYear = date('Y', time());
|
---|
[873] | 81 | for ($Year = $StartYear; $Year <= $EndYear; $Year++)
|
---|
[847] | 82 | {
|
---|
| 83 | $EndTime = mktime(0, 0, 0, 12, 31, $Year);
|
---|
| 84 | //$Year = date('Y', $EndTime);
|
---|
| 85 | $StartTime = mktime(0, 0, 0, 1, 1, $Year);
|
---|
[873] | 86 | if ($StartTime < $this->StartEvidence) $StartTime = $this->StartEvidence;
|
---|
[748] | 87 |
|
---|
[847] | 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 |
|
---|
[888] | 108 | function ShowMonthlyBalance(): string
|
---|
[847] | 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());
|
---|
[873] | 117 | for ($Year = $StartYear; $Year <= $EndYear; $Year++)
|
---|
[152] | 118 | {
|
---|
[873] | 119 | for ($Month = 1; $Month <= 12; $Month++)
|
---|
[847] | 120 | {
|
---|
| 121 | $EndTime = mktime(0, 0, 0, $Month, 31, $Year);
|
---|
| 122 | //$Year = date('Y', $EndTime);
|
---|
| 123 | $StartTime = mktime(0, 0, 0, $Month, 1, $Year);
|
---|
[873] | 124 | if (($StartTime < time()) and ($EndTime > $this->StartEvidence))
|
---|
[152] | 125 | {
|
---|
[873] | 126 | if ($StartTime < $this->StartEvidence) $StartTime = $this->StartEvidence;
|
---|
[70] | 127 |
|
---|
[847] | 128 | $Balance = $this->GetTimePeriodBalance($StartTime, $EndTime);
|
---|
[70] | 129 |
|
---|
[847] | 130 | $Output .= '<tr><td>'.$Year.'</td><td>'.$Month.'</td><td>'.date(HumanDate(TimeToMysqlDateTime($StartTime))).'</td><td>'.date(HumanDate(TimeToMysqlDateTime($EndTime))).'</td>';
|
---|
[338] | 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>';
|
---|
[70] | 136 |
|
---|
[338] | 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>';
|
---|
[152] | 143 | }
|
---|
[847] | 144 | }
|
---|
| 145 | }
|
---|
| 146 | $Output .= '</table>';
|
---|
| 147 | return $Output;
|
---|
| 148 | }
|
---|
[152] | 149 |
|
---|
[888] | 150 | function ShowIncomes(): string
|
---|
[847] | 151 | {
|
---|
| 152 | $Table = array('Ne', 'Ano');
|
---|
| 153 | $DbResult = $this->Database->select('FinanceYear', '*', 'Id='.$_GET['year']);
|
---|
| 154 | $Year = $DbResult->fetch_assoc();
|
---|
[70] | 155 |
|
---|
[847] | 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`');
|
---|
[873] | 168 | while ($Row = $DbResult->fetch_array())
|
---|
[847] | 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 | }
|
---|
[165] | 188 |
|
---|
[888] | 189 | function ShowExpenses(): string
|
---|
[847] | 190 | {
|
---|
| 191 | $Table = array('Ne', 'Ano');
|
---|
| 192 | $DbResult = $this->Database->select('FinanceYear', '*', 'Id='.$_GET['year']);
|
---|
| 193 | $Year = $DbResult->fetch_assoc();
|
---|
[165] | 194 |
|
---|
[847] | 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`');
|
---|
[873] | 208 | while ($Row = $DbResult->fetch_array())
|
---|
[847] | 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 |
|
---|
[888] | 230 | function ShowClaims(): string
|
---|
[847] | 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`');
|
---|
[873] | 249 | while ($Row = $DbResult->fetch_array())
|
---|
[847] | 250 | {
|
---|
[873] | 251 | if ($Row['TimePayment'] == '0000-00-00 00:00:00') $Row['TimePayment'] = ' ';
|
---|
[847] | 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 |
|
---|
[888] | 261 | function ShowLiabilities(): string
|
---|
[847] | 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');
|
---|
[873] | 280 | while ($Row = $DbResult->fetch_array())
|
---|
[847] | 281 | {
|
---|
[873] | 282 | if ($Row['TimePayment'] == '0000-00-00 00:00:00') $Row['TimePayment'] = ' ';
|
---|
[847] | 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 |
|
---|
[888] | 293 | function ShowSubjectList(): string
|
---|
[847] | 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');
|
---|
[873] | 318 | while ($Row = $DbResult->fetch_assoc())
|
---|
[847] | 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 |
|
---|
[888] | 326 | function ShowSmallAssets(): string
|
---|
[847] | 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)');
|
---|
[873] | 334 | while ($Row = $DbResult->fetch_array())
|
---|
[847] | 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 |
|
---|
[888] | 342 | function ShowSubjectAccount(): string
|
---|
[847] | 343 | {
|
---|
[887] | 344 | $Finance = &ModuleFinance::Cast($this->System->GetModule('Finance'))->Finance;
|
---|
| 345 |
|
---|
[847] | 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`');
|
---|
[873] | 355 | while ($Row = $DbResult->fetch_array())
|
---|
[847] | 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`');
|
---|
[873] | 370 | while ($Row = $DbResult->fetch_array())
|
---|
[847] | 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']);
|
---|
[873] | 381 | while ($Row = $DbResult->fetch_array())
|
---|
[847] | 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 |
|
---|
[888] | 407 | function ShowDefault(): string
|
---|
[847] | 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();
|
---|
[873] | 417 | if (array_key_exists('year', $_GET))
|
---|
[847] | 418 | {
|
---|
| 419 | $Year = $_GET['year'] * 1;
|
---|
| 420 | $Output .= '<a href="?table=Incomes&year='.$Year.'">Příjmy</a><br />';
|
---|
| 421 | $Output .= '<a href="?table=Expenses&year='.$Year.'">Výdaje</a><br />';
|
---|
| 422 | $Output .= '<a href="?table=Claims&year='.$Year.'">Pohledávky</a><br />';
|
---|
| 423 | $Output .= '<a href="?table=Liabilities&year='.$Year.'">Závazky</a><br />';
|
---|
| 424 | $Output .= '<a href="?table=Advantages&year='.$Year.'">Zálohy</a><br />';
|
---|
| 425 | $Output .= '<a href="?table=YearReport&year='.$Year.'">Souhrný přehled</a><br />';
|
---|
| 426 | }
|
---|
| 427 | return $Output;
|
---|
| 428 | }
|
---|
| 429 |
|
---|
[887] | 430 | function Show(): string
|
---|
[847] | 431 | {
|
---|
[887] | 432 | if (!ModuleUser::Cast($this->System->GetModule('User'))->User->CheckPermission('Finance', 'TradingStatus'))
|
---|
[874] | 433 | return 'Nemáte oprávnění';
|
---|
[847] | 434 |
|
---|
| 435 | $Output = '';
|
---|
[873] | 436 | if (!array_key_exists('table', $_GET)) $_GET['table'] = '';
|
---|
| 437 | switch ($_GET['table'])
|
---|
[847] | 438 | {
|
---|
| 439 | case 'AnnualBalance':
|
---|
| 440 | $Output = $this->ShowAnnualBalance();
|
---|
[152] | 441 | break;
|
---|
[847] | 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;
|
---|
[397] | 451 | case 'Incomes':
|
---|
[847] | 452 | $Output = $this->ShowIncomes();
|
---|
[152] | 453 | break;
|
---|
[397] | 454 | case 'Expenses':
|
---|
[847] | 455 | $Output = $this->ShowExpenses();
|
---|
[397] | 456 | break;
|
---|
| 457 | case 'Claims':
|
---|
[847] | 458 | $Output = $this->ShowClaims();
|
---|
[397] | 459 | break;
|
---|
[847] | 460 | case 'Liabilities':
|
---|
| 461 | $Output = $this->ShowLiabilities();
|
---|
[152] | 462 | break;
|
---|
| 463 | case 'SubjectList':
|
---|
[847] | 464 | $Output = $this->ShowSubjectList();
|
---|
[152] | 465 | break;
|
---|
| 466 | case 'SmallAssets':
|
---|
[847] | 467 | $Output = $this->ShowSmallAssets();
|
---|
[152] | 468 | break;
|
---|
| 469 | case 'SubjectAccount':
|
---|
[847] | 470 | $Output = $this->ShowSubjectAccount();
|
---|
[152] | 471 | break;
|
---|
[243] | 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;
|
---|
[152] | 479 | default:
|
---|
[847] | 480 | $Output = $this->ShowDefault();
|
---|
[70] | 481 | }
|
---|
[874] | 482 | return $Output;
|
---|
[152] | 483 | }
|
---|
[738] | 484 |
|
---|
[888] | 485 | function ShowFinanceYears(): string
|
---|
[397] | 486 | {
|
---|
| 487 | $Output = 'Roky: ';
|
---|
| 488 | $DbRows = $this->Database->select('FinanceYear', '*');
|
---|
[873] | 489 | while ($DbRow = $DbRows->fetch_assoc())
|
---|
[397] | 490 | $Output .= '<a href="?year='.$DbRow['Id'].'">'.$DbRow['Year'].'</a> ';
|
---|
| 491 | $Output .= '<br/>';
|
---|
[874] | 492 | return $Output;
|
---|
[397] | 493 | }
|
---|
[70] | 494 |
|
---|
[152] | 495 | function AddMoney($Subject, $Value, $Text, $Time)
|
---|
| 496 | {
|
---|
| 497 | $TimeDue = $Time + 15 * 24 * 3600; // 15 dnů
|
---|
| 498 | }
|
---|
[70] | 499 |
|
---|
[152] | 500 | function AddInvoice($Subject, $Value, $Text, $Time)
|
---|
| 501 | {
|
---|
| 502 | $TimeDue = $Time + 15 * 24 * 3600; // 15 dnů
|
---|
| 503 | }
|
---|
[70] | 504 | }
|
---|