Query('SELECT user as Subject, date as TimeCreation, date as TimeDue, date as TimePayment, -money as Value, bill_id as Bill, comment as Text FROM `finance_operations` WHERE Comment LIKE "Poplatek za měsíc%" AND date >= "2007-12-01"'); //'TRUNCATE TABLE FinanceCashFlow; INSERT INTO FinanceCashFlow (SELECT '' as Id, user as Subject, comment as Text, date as Time, 1 as Taxable, 0 as Cash, money as Value, bill_id as Bill FROM `finance_operations` WHERE comment NOT LIKE "Poplatek za měsíc%" AND date >= "2007-12-01")' // 'UPDATE finance_operations SET date="2007-11-24" WHERE comment="Jednorázový poplatek za společné zařízení"' $DbResult = $this->Database->query('SELECT * FROM users WHERE role=2'); while($User = $DbResult->fetch_array()) { $DbResult2 = $this->Database->query('SELECT SUM(money) FROM finance_operations WHERE user='.$User['id'].' AND date < "2007-12-01"'); $Row = $DbResult2->fetch_array(); echo($User['second_name'].' '.$User['first_name'].' '.$Row[0].'
'); // if($Row[0] > 0) $Database->insert('FinanceCashFlow', array('Text' => 'Přijatá záloha (z období před účetnictvím)', 'Subject' => $User['id'], 'Time' => '2007-01-01', 'Taxable' => 1, 'Cash' => 0, 'Value' => $Row[0], 'Bill' => 0)); if($Row[0] < 0) { $this->Database->insert('FinanceClaimsLiabilities', array('Text' => 'Připojení k síti (z období před účetnictvím)', 'Subject' => $User['id'], 'TimeCreation' => '2007-12-01', 'TimeDue' => '2007-12-14', 'TimePayment' => '', 'Value' => (-$Row[0]), 'Bill' => 0)); //echo($Database->LastQuery.'
'); } } }*/ function GetTimePeriodBalance($StartTime, $EndTime) { $Balance = array(); $DbResult = $this->Database->query('SELECT SUM(Value) FROM FinanceOperation WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Taxable = 1) AND (Value > 0)'); $Row = $DbResult->fetch_array(); $Balance['Income']['Start'] = $Row[0] + 0; $DbResult = $this->Database->query('SELECT SUM(Value) FROM FinanceOperation WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Taxable = 1) AND (Value > 0)'); //echo('SELECT SUM(Value) FROM FinanceCashFlow WHERE Time <= "'.TimeToMysqlDateTime($EndTime).'" AND Value > 0 AND Taxable = 1'.'
'); $Row = $DbResult->fetch_array(); $Balance['Income']['End'] = $Row[0] + 0; $DbResult = $this->Database->query('SELECT SUM(-Value) FROM FinanceOperation WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Taxable = 1) AND (Value < 0)'); $Row = $DbResult->fetch_array(); $Balance['Spend']['Start'] = $Row[0] + 0; $DbResult = $this->Database->query('SELECT SUM(-Value) FROM FinanceOperation WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Taxable = 1) AND (Value < 0)'); //echo('SELECT -SUM(Value) FROM FinanceCashFlow WHERE Time <= "'.TimeToMysqlDateTime($EndTime).'" AND Value < 0 AND Taxable = 1'.'
'); $Row = $DbResult->fetch_array(); $Balance['Spend']['End'] = $Row[0] + 0; $DbResult = $this->Database->query('SELECT SUM(Value) FROM FinanceClaimsLiabilities WHERE (TimeCreation < "'.TimeToMysqlDateTime($StartTime).'") AND (TimeCreation >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Value > 0)'); $Row = $DbResult->fetch_array(); $Balance['Claims']['Start'] = $Row[0] + 0; $DbResult = $this->Database->query('SELECT SUM(Value) FROM FinanceClaimsLiabilities WHERE (TimeCreation <= "'.TimeToMysqlDateTime($EndTime).'") AND (TimeCreation >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Value > 0)'); $Row = $DbResult->fetch_array(); $Balance['Claims']['End'] = $Row[0] + 0; $DbResult = $this->Database->query('SELECT SUM(-Value) FROM FinanceClaimsLiabilities WHERE (TimeCreation < "'.TimeToMysqlDateTime($StartTime).'") AND (TimeCreation >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Value < 0)'); $Row = $DbResult->fetch_array(); $Balance['Liabilities']['Start'] = $Row[0] + 0; $DbResult = $this->Database->query('SELECT SUM(-Value) FROM FinanceClaimsLiabilities WHERE (TimeCreation <= "'.TimeToMysqlDateTime($EndTime).'") AND (TimeCreation >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Value < 0)'); $Row = $DbResult->fetch_array(); $Balance['Liabilities']['End'] = $Row[0] + 0; // SmallAssets $DbResult = $this->Database->query('SELECT SUM(price) FROM network_devices WHERE (TimeEnlistment < "'.TimeToMysqlDateTime($StartTime).'") AND (TimeEnlistment != 0) AND ((TimeElimination > "'.TimeToMysqlDateTime($StartTime).'") OR (TimeElimination = 0))'); //$Output .= $Database->error; $Row = $DbResult->fetch_array(); $Balance['SmallAssets']['Start'] = $Row[0] + 0; $DbResult = $this->Database->query('SELECT SUM(price) FROM network_devices WHERE (TimeEnlistment < "'.TimeToMysqlDateTime($EndTime).'") AND (TimeEnlistment != 0) AND ((TimeElimination > "'.TimeToMysqlDateTime($EndTime).'") OR (TimeElimination = 0))'); $Row = $DbResult->fetch_array(); $Balance['SmallAssets']['End'] = $Row[0] + 0; return($Balance); } function Show() { if(!$this->System->Modules['User']->CheckPermission('Finance', 'TradingStatus')) return('Nemáte oprávnění'); $this->System->Modules['Finance']->LoadTariffs(1); //TransformFinance(); $Output = ''; if(!array_key_exists('table', $_GET)) $_GET['table'] = ''; switch($_GET['table']) { case 'AnnualBalance': $Output = ''; $Output .= ''. ''; $this->StartEvidence = mktime(0, 0, 0, 12, 1, 2007); $StartYear = date('Y', $this->StartEvidence); $EndYear = date('Y', time()); for($Year = $StartYear; $Year <= $EndYear; $Year++) { $EndTime = mktime(0, 0, 0, 12, 31, $Year); //$Year = date('Y', $EndTime); $StartTime = mktime(0, 0, 0, 1, 1, $Year); if($StartTime < $this->StartEvidence) $StartTime = $this->StartEvidence; $Balance = $this->GetTimePeriodBalance($StartTime, $EndTime); $Output .= ''; $Output .= ''; $Output .= ''; $Output .= ''; $Output .= ''; $Output .= ''; $Output .= ''; $Output .= ''; $Output .= ''; $Output .= ''; $Output .= ''; $Output .= ''; } $Output .= '

Účetní období			Na konci					Během
Rok	Od	Do	Příjmy	Výdaje	Pohledávky	Závazky	Drobný majetek	Příjmy	Výdaje	Pohledávky	Závazky	Drobný majetek	Základ daně
'.$Year.'	'.date(HumanDate(TimeToMysqlDateTime($StartTime))).'	'.date(HumanDate(TimeToMysqlDateTime($EndTime))).'	'.$Balance['Income']['End'].'	'.$Balance['Spend']['End'].'	'.$Balance['Claims']['End'].'	'.$Balance['Liabilities']['End'].'	'.$Balance['SmallAssets']['End'].'	'.($Balance['Income']['End'] - $Balance['Income']['Start']).'	'.($Balance['Spend']['End'] - $Balance['Spend']['Start']).'	'.($Balance['Claims']['End'] - $Balance['Claims']['Start']).'	'.($Balance['Liabilities']['End'] - $Balance['Liabilities']['Start']).'	'.($Balance['SmallAssets']['End'] - $Balance['SmallAssets']['Start']).'	'.(($Balance['Income']['End'] - $Balance['Income']['Start']) - ($Balance['Spend']['End'] - $Balance['Spend']['Start'])).'

'; break; case 'MonthlyBalance': $Output = ''; $Output .= ''. ''; $this->StartEvidence = mktime(0, 0, 0, 12, 1, 2007); $StartYear = date('Y', $this->StartEvidence); $EndYear = date('Y', time()); for($Year = $StartYear; $Year <= $EndYear; $Year++) { for($Month = 1; $Month <= 12; $Month++) { $EndTime = mktime(0, 0, 0, $Month, 31, $Year); //$Year = date('Y', $EndTime); $StartTime = mktime(0, 0, 0, $Month, 1, $Year); if(($StartTime < time()) and ($EndTime > $this->StartEvidence)) { if($StartTime < $this->StartEvidence) $StartTime = $this->StartEvidence; $Balance = $this->GetTimePeriodBalance($StartTime, $EndTime); $Output .= ''; $Output .= ''; $Output .= ''; $Output .= ''; $Output .= ''; $Output .= ''; $Output .= ''; $Output .= ''; $Output .= ''; $Output .= ''; $Output .= ''; $Output .= ''; } } } $Output .= '

Účetní období				Na konci					Během
Rok	Měsíc	Od	Do	Příjmy	Výdaje	Pohledávky	Závazky	Drobný majetek	Příjmy	Výdaje	Pohledávky	Závazky	Drobný majetek	Základ daně
'.$Year.'	'.$Month.'	'.date(HumanDate(TimeToMysqlDateTime($StartTime))).'	'.date(HumanDate(TimeToMysqlDateTime($EndTime))).'	'.$Balance['Income']['End'].'	'.$Balance['Spend']['End'].'	'.$Balance['Claims']['End'].'	'.$Balance['Liabilities']['End'].'	'.$Balance['SmallAssets']['End'].'	'.($Balance['Income']['End'] - $Balance['Income']['Start']).'	'.($Balance['Spend']['End'] - $Balance['Spend']['Start']).'	'.($Balance['Claims']['End'] - $Balance['Claims']['Start']).'	'.($Balance['Liabilities']['End'] - $Balance['Liabilities']['Start']).'	'.($Balance['SmallAssets']['End'] - $Balance['SmallAssets']['Start']).'	'.(($Balance['Income']['End'] - $Balance['Income']['Start']) - ($Balance['Spend']['End'] - $Balance['Spend']['Start'])).'

'; break; case 'CashFlow': $Table = array('Ne', 'Ano'); $Output .= ''; $Output .= ''; $DbResult = $this->Database->query('SELECT * FROM FinanceOperation LEFT JOIN Subject ON Subject.Id = FinanceOperation.Subject ORDER BY Time'); while($Row = $DbResult->fetch_array()) { $Row['Time'] = explode(' ', $Row['Time']); $Row['Time'] = $Row['Time'][0]; $Output .= ''; } $Output .= '

Čas	Subjekt	Text	Hodnota [Kč]	Daňový	Hotovost
'.$Row['Time'].'	'.$Row['Name'].'	'.$Row['Text'].'	'.$Row['Value'].'	'.$Table[$Row['Taxable']].'	'.$Table[$Row['Cash']].'

'; break; case 'ClaimsLiabilities': $Output .= '

'; $Output .= 'Pohledávky'; $Output .= ''; $Output .= ''; $DbResult = $this->Database->query('SELECT * FROM FinanceClaimsLiabilities JOIN Subject ON Subject.Id = FinanceClaimsLiabilities.Subject WHERE Value > 0 ORDER BY TimeCreation'); while($Row = $DbResult->fetch_array()) { if($Row['TimePayment'] == '0000-00-00 00:00:00') $Row['TimePayment'] = ' '; $Output .= ''; } $Output .= ' Subjekt Text Hodnota [Kč] Čas vystavení Čas splatnosti Čas uhrazení '.$Row['Name'].' '.$Row['Text'].' '.$Row['Value'].' '.$Row['TimeCreation'].' '.$Row['TimeDue'].' '.$Row['TimePayment'].'

'; $Output .= 'Závazky'; $Output .= ''; $Output .= ''; $DbResult = $this->Database->query('SELECT * FROM FinanceClaimsLiabilities JOIN Subject ON Subject.Id = FinanceClaimsLiabilities.Subject WHERE Value < 0 ORDER BY TimeCreation'); while($Row = $DbResult->fetch_array()) { if($Row['TimePayment'] == '0000-00-00 00:00:00') $Row['TimePayment'] = ' '; $Output .= ''; } $Output .= ' Subjekt Text Hodnota [Kč] Čas vystavení Čas splatnosti Čas uhrazení '.$Row['Name'].' '.$Row['Text'].' '.$Row['Value'].' '.$Row['TimeCreation'].' '.$Row['TimeDue'].' '.$Row['TimePayment'].'

'; break; case 'SubjectList': $Output .= 'Seznam subjektů'; $Output .= ''; $Output .= ''; $DbResult = $this->Database->query('SELECT Id, Name, 0 AS Cash, '. '(SELECT SUM(T1.Value) FROM FinanceClaimsLiabilities AS T1 WHERE (T1.Subject = Subject.Id) AND (T1.Value > 0)) as Claims, '. '(SELECT SUM(T2.Value) FROM FinanceClaimsLiabilities AS T2 WHERE (T2.Subject = Subject.Id) AND (T2.Value > 0) AND (TimePayment IS NULL)) as OpenedClaims, '. '(SELECT -SUM(T3.Value) FROM FinanceClaimsLiabilities AS T3 WHERE (T3.Subject = Subject.Id) AND (T3.Value < 0)) as Liabilities, '. '(SELECT -SUM(T4.Value) FROM FinanceClaimsLiabilities AS T4 WHERE (T4.Subject = Subject.Id) AND (T4.Value < 0) AND (TimePayment IS NULL)) AS OpenedLiabilities, '. '(SELECT SUM(T5.Value) FROM FinanceOperation AS T5 WHERE (T5.Subject = Subject.Id) AND (T5.Value > 0)) AS Gains, '. '(SELECT -SUM(T6.Value) FROM FinanceOperation AS T6 WHERE (T6.Subject = Subject.Id) AND (T6.Value < 0)) AS Spends '. 'FROM Subject ORDER BY Name'); while($Row = $DbResult->fetch_assoc()) { $Output .= ''; } $Output .= '

Jméno	Závazky [Kč]	Pohledávky [Kč]	Příjmy [Kč]	Výdaje [Kč]	Stav účtu	Starý systém
'.$Row['Name'].'	'.$Row['Liabilities'].' / '.$Row['OpenedLiabilities'].'	'.$Row['Claims'].' / '.$Row['OpenedClaims'].'	'.$Row['Gains'].'	'.$Row['Spends'].'	'.($Row['Gains'] - $Row['Spends'] - $Row['Claims'] + $Row['Liabilities']).'	'.$Row['Cash'].'

'; break; case 'SmallAssets': $Output .= 'Drobný majetek'; $Output .= ''; $Output .= ''; $DbResult = $this->Database->query('SELECT * FROM network_devices WHERE used != 0'); while($Row = $DbResult->fetch_array()) { $Output .= ''; } $Output .= '

Název	Hodnota [Kč]	Datum zakoupení	Datum vyřezení
'.$Row['name'].'	'.$Row['price'].'	'.$Row['TimeEnlistment'].'	'.$Row['TimeElimination'].'

'; break; case 'SubjectAccount': $Output .= 'Výpis příjmů/výdajů'; $Output .= ''; $Output .= ''; $DbResult = $this->Database->select('FinanceCashFlow', '*', 'Subject='.$_GET['Id']); while($Row = $DbResult->fetch_array()) { $Output .= ''; } $Output .= '

Datum	Název	Hodnota [Kč]
'.$Row['Time'].'	'.$Row['Text'].'	'.$Row['Value'].'	'.$Row['Bill'].'

'; $Output .= 'Výpis závazků/pohledávek'; $Output .= ''; $Output .= ''; $DbResult = $this->Database->select('FinanceClaimsLiabilities', '*', 'Subject='.$_GET['Id']); while($Row = $DbResult->fetch_array()) { $Output .= ''; } $Output .= '

Datum vytvoření	Datum zaplacení	Název	Hodnota [Kč]
'.$Row['TimeCreation'].'	'.$Row['TimePayment'].'	'.$Row['Text'].'	'.(-$Row['Value']).'	'.$Row['Bill'].'

'; $Output .= 'Výpis záloh'; $Output .= ''; $Output .= ''; $DbResult = $this->Database->select('FinanceAdvances', '*', 'Subject='.$_GET['Id']); while($Row = $DbResult->fetch_array()) { $Output .= ''; } $Output .= '

Datum vytvoření	Datum zaplacení	Název	Hodnota [Kč]
'.$Row['TimeCreation'].'	'.$Row['TimePass'].'	'.$Row['Direction'].'	'.($Row['Value']).'	'.$Row['CashFlowId'].'

'; $DbResult = $this->Database->query('SELECT Id, 0 AS Cash, (SELECT SUM(FinanceClaimsLiabilities.Value) FROM FinanceClaimsLiabilities WHERE FinanceClaimsLiabilities.Subject = Subject.Id AND FinanceClaimsLiabilities.Value > 0) as Claims, (SELECT SUM(FinanceClaimsLiabilities.Value) FROM FinanceClaimsLiabilities WHERE FinanceClaimsLiabilities.Subject = Subject.Id AND FinanceClaimsLiabilities.Value > 0 AND TimePayment IS NULL) as OpenedClaims, (SELECT SUM(FinanceClaimsLiabilities.Value) FROM FinanceClaimsLiabilities WHERE FinanceClaimsLiabilities.Subject = Subject.Id AND FinanceClaimsLiabilities.Value < 0) as Liabilities, (SELECT SUM(FinanceClaimsLiabilities.Value) FROM FinanceClaimsLiabilities WHERE FinanceClaimsLiabilities.Subject = Subject.Id AND FinanceClaimsLiabilities.Value < 0 AND TimePayment IS NULL) as OpenedLiabilities, (SELECT SUM(FinanceCashFlow.Value) FROM FinanceCashFlow WHERE FinanceCashFlow.Subject = Subject.Id AND FinanceCashFlow.Value > 0) as Gains, (SELECT SUM(FinanceCashFlow.Value) FROM FinanceCashFlow WHERE FinanceCashFlow.Subject = Subject.Id AND FinanceCashFlow.Value < 0) as Spends, (SELECT SUM(FinanceAdvances.Value) FROM FinanceAdvances WHERE FinanceAdvances.Subject = Subject.Id AND FinanceAdvances.Direction="In") as AdvancesIn, (SELECT SUM(FinanceAdvances.Value) FROM FinanceAdvances WHERE FinanceAdvances.Subject = Subject.Id AND FinanceAdvances.Direction="Out") as AdvancesOut FROM Subject WHERE Id='.$_GET['Id']); $Row = $DbResult->fetch_array(); $Output .= 'Stav placení: '.($Row['AdvancesIn'] - $Row['AdvancesOut'] - $Row['OpenedClaims'] + Abs($Row['OpenedLiabilities'])); break; case 'PrintMonthOperations': $Output .= '

Datum	Subjekt

'; break; case 'PrintMonthOperations': $Output .= 'Výpis operací od '.$_GET['TimeFrom'].' do '.$_GET['TimeTo']; $Output .= '

Datum	Subjekt	Suma [Kč]

'; break; default: $Output .= 'Účetní závěrka
'; $Output .= 'Měsíční přehledy
'; $Output .= 'Příjmy a výdaje
'; $Output .= 'Závazky a pohledávky
'; $Output .= 'Zálohy
'; $Output .= 'Drobný majetek
'; $Output .= 'Seznam subjektů
'; } return($Output); } function AddMoney($Subject, $Value, $Text, $Time) { $TimeDue = $Time + 15 * 24 * 3600; // 15 dnů } function AddInvoice($Subject, $Value, $Text, $Time) { $TimeDue = $Time + 15 * 24 * 3600; // 15 dnů } } $System->AddModule(new FinanceTaxFilingPage()); $System->Modules['FinanceTaxFilingPage']->GetOutput(); ?>