<?php

class PageFinanceTaxFiling extends Page
{
  public int $StartEvidence = 0;

  function __construct(System $System)
  {
    parent::__construct($System);
    $this->Title = 'Daňová evidence';
    $this->ParentClass = 'PageFinance';
  }

  function GetTimePeriodBalance(int $StartTime, int $EndTime): array
  {
    $Balance = array();
    $DbResult = $this->Database->query('SELECT SUM(`Value`) FROM `FinanceOperation` '.
      'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id` = `FinanceOperation`.`Group` '.
      'WHERE (`Time` < "'.TimeToMysqlDateTime($StartTime).'") AND (`Time` >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (`Taxable` = 1) AND (`FinanceOperationGroup`.`ValueSign` = 1)');
    $Row = $DbResult->fetch_array();
    $Balance['Income']['Start'] = $Row[0] + 0;
    $DbResult = $this->Database->query('SELECT SUM(Value) FROM FinanceOperation '.
      'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id` = `FinanceOperation`.`Group` '.
      'WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Taxable = 1) AND (`FinanceOperationGroup`.`ValueSign` = 1)');
    $Row = $DbResult->fetch_array();
    $Balance['Income']['End'] = $Row[0] + 0;

    $DbResult = $this->Database->query('SELECT SUM(Value*ValueSign) FROM FinanceOperation '.
      'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id` = `FinanceOperation`.`Group` '.
      'WHERE (`Time` < "'.TimeToMysqlDateTime($StartTime).'") AND (`Time` >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (`Taxable` = 1) AND (`FinanceOperationGroup`.`ValueSign` = -1)');
    $Row = $DbResult->fetch_array();
    $Balance['Spend']['Start'] = $Row[0] + 0;
    $DbResult = $this->Database->query('SELECT SUM(Value*ValueSign) FROM FinanceOperation '.
      'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id` = `FinanceOperation`.`Group` '.
      'WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Taxable = 1) AND (`FinanceOperationGroup`.`ValueSign` = -1)');
    $Row = $DbResult->fetch_array();
    $Balance['Spend']['End'] = $Row[0] + 0;

    $DbResult = $this->Database->query('SELECT SUM(Value*ValueSign) FROM FinanceInvoice '.
      'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `FinanceInvoice`.`Group` '.
      'WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (`FinanceInvoiceGroup`.`ValueSign` = 1)');
    $Row = $DbResult->fetch_array();
    $Balance['Claims']['Start'] = $Row[0] + 0;
    $DbResult = $this->Database->query('SELECT SUM(Value*ValueSign) FROM FinanceInvoice '.
      'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `FinanceInvoice`.`Group` '.
      'WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (`FinanceInvoiceGroup`.`ValueSign` = 1)');
    $Row = $DbResult->fetch_array();
    $Balance['Claims']['End'] = $Row[0] + 0;

    $DbResult = $this->Database->query('SELECT SUM(Value*ValueSign) FROM FinanceInvoice '.
      'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `FinanceInvoice`.`Group` '.
      'WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (`FinanceInvoiceGroup`.`ValueSign` = -1)');
    $Row = $DbResult->fetch_array();
    $Balance['Liabilities']['Start'] = $Row[0] + 0;
    $DbResult = $this->Database->query('SELECT SUM(Value*ValueSign) FROM FinanceInvoice '.
      'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `FinanceInvoice`.`Group` '.
      'WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (`FinanceInvoiceGroup`.`ValueSign` = -1)');
    $Row = $DbResult->fetch_array();
    $Balance['Liabilities']['End'] = $Row[0] + 0;

    // SmallAssets
    $DbResult = $this->Database->query('SELECT SUM(SellPrice) FROM StockSerialNumber WHERE (TimeEnlistment < "'.TimeToMysqlDateTime($StartTime).'") AND (TimeEnlistment != 0) AND ((TimeElimination > "'.TimeToMysqlDateTime($StartTime).'") OR (TimeElimination IS NULL))');
    //$Output .= $Database->error;
    $Row = $DbResult->fetch_array();
    $Balance['SmallAssets']['Start'] = $Row[0] + 0;
    $DbResult = $this->Database->query('SELECT SUM(SellPrice) FROM StockSerialNumber WHERE (TimeEnlistment < "'.TimeToMysqlDateTime($EndTime).'") AND (TimeEnlistment != 0) AND ((TimeElimination > "'.TimeToMysqlDateTime($EndTime).'") OR (TimeElimination IS NULL))');
    $Row = $DbResult->fetch_array();
    $Balance['SmallAssets']['End'] = $Row[0] + 0;
    return $Balance;
  }

  function ShowAnnualBalance(): string
  {
    $Output = '<table style="font-size: smaller;" class="WideTable">';
    $Output .= '<tr><th colspan="3">Účetní období</th><th colspan="5">Na konci</th><th colspan="6">Během</th></tr>'.
      '<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>';

    $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 .= '<tr><td>'.$Year.'</td><td>'.date(HumanDate(TimeToMysqlDateTime($StartTime))).'</td><td>'.date(HumanDate(TimeToMysqlDateTime($EndTime))).'</td>';
      $Output .= '<td>'.round($Balance['Income']['End']).'</td>';
      $Output .= '<td>'.round($Balance['Spend']['End']).'</td>';
      $Output .= '<td>'.round($Balance['Claims']['End']).'</td>';
      $Output .= '<td>'.round($Balance['Liabilities']['End']).'</td>';
      $Output .= '<td>'.round($Balance['SmallAssets']['End']).'</td>';

      $Output .= '<td>'.round($Balance['Income']['End'] - $Balance['Income']['Start']).'</td>';
      $Output .= '<td>'.round($Balance['Spend']['End'] - $Balance['Spend']['Start']).'</td>';
      $Output .= '<td>'.round($Balance['Claims']['End'] - $Balance['Claims']['Start']).'</td>';
      $Output .= '<td>'.round($Balance['Liabilities']['End'] - $Balance['Liabilities']['Start']).'</td>';
      $Output .= '<td>'.round($Balance['SmallAssets']['End'] - $Balance['SmallAssets']['Start']).'</td>';
      $Output .= '<td>'.round(($Balance['Income']['End'] - $Balance['Income']['Start']) - ($Balance['Spend']['End'] - $Balance['Spend']['Start'])).'</td></tr>';
    }
    $Output .= '</table>';
    return $Output;
  }

  function ShowMonthlyBalance(): string
  {
    $Output = '<table class="WideTable" style="font-size: smaller;">';
    $Output .= '<tr><th colspan="4">Účetní období</th><th colspan="5">Na konci</th><th colspan="6">Během</th></tr>'.
      '<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>';

    $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 .= '<tr><td>'.$Year.'</td><td>'.$Month.'</td><td>'.date(HumanDate(TimeToMysqlDateTime($StartTime))).'</td><td>'.date(HumanDate(TimeToMysqlDateTime($EndTime))).'</td>';
          $Output .= '<td>'.round($Balance['Income']['End']).'</td>';
          $Output .= '<td>'.round($Balance['Spend']['End']).'</td>';
          $Output .= '<td>'.round($Balance['Claims']['End']).'</td>';
          $Output .= '<td>'.round($Balance['Liabilities']['End']).'</td>';
          $Output .= '<td>'.round($Balance['SmallAssets']['End']).'</td>';

          $Output .= '<td>'.round($Balance['Income']['End'] - $Balance['Income']['Start']).'</td>';
          $Output .= '<td>'.round($Balance['Spend']['End'] - $Balance['Spend']['Start']).'</td>';
          $Output .= '<td>'.round($Balance['Claims']['End'] - $Balance['Claims']['Start']).'</td>';
          $Output .= '<td>'.round($Balance['Liabilities']['End'] - $Balance['Liabilities']['Start']).'</td>';
          $Output .= '<td>'.round($Balance['SmallAssets']['End'] - $Balance['SmallAssets']['Start']).'</td>';
          $Output .= '<td>'.round(($Balance['Income']['End'] - $Balance['Income']['Start']) - ($Balance['Spend']['End'] - $Balance['Spend']['Start'])).'</td></tr>';
        }
      }
    }
    $Output .= '</table>';
    return $Output;
  }

  function ShowIncomes(): string
  {
    $Table = array('Ne', 'Ano');
    $DbResult = $this->Database->select('FinanceYear', '*', 'Id='.$_GET['year']);
    $Year = $DbResult->fetch_assoc();

    $Total = 0;
    $TotalTax = array(0, 0);
    $TotalCash = array(0, 0);
    $Output = '<strong>Příjmy za rok '.$Year['Year'].'</strong>';
    $Output .= '<table style="font-size: smaller;" class="WideTable">';
    $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>';
    $DbResult = $this->Database->query('SELECT `FinanceOperation`.*, `DocumentLineCode`.`Name` AS `BillName`, `Subject`.`Name` AS `Name` FROM `FinanceOperation` '.
      'LEFT JOIN `Subject` ON `Subject`.`Id` = `FinanceOperation`.`Subject` '.
      'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id` = `FinanceOperation`.`Group` '.
      'LEFT JOIN `DocumentLineCode` ON `DocumentLineCode`.`Id` = `FinanceOperation`.`BillCode` '.
      'WHERE (`ValueSign` = 1) AND (`FinanceOperation`.`Time` >= "'.$Year['DateStart'].'") '.
      'AND (`FinanceOperation`.`Time` <= "'.$Year['DateEnd'].'") ORDER BY `Time`');
    while ($Row = $DbResult->fetch_array())
    {
      $Row['Time'] = explode(' ', $Row['Time']);
      $Row['Time'] = $Row['Time'][0];
      $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.$Row['BillName'].
      '</td><td>'.$Row['Name'].'</td><td>'.$Row['Text'].'</td><td>'.$Row['Value'].
      '</td><td>'.$Table[$Row['Taxable']].'</td><td>'.$Table[$Row['Cash']].'</td></tr>';
      $Total += $Row['Value'];
      $TotalTax[$Row['Taxable']] += $Row['Value'];
      $TotalCash[$Row['Cash']] += $Row['Value'];
    }
    $Output .= '</table><br/>';
    $Output .= '<strong>Souhrn</strong><br/>';
    $Output .= 'Celkem: '.$Total.' Kč<br/>';
    $Output .= '  Daňové: '.$TotalTax[1].' Kč<br/>';
    $Output .= '  Nedaňové: '.$TotalTax[0].' Kč<br/>';
    $Output .= '  Hotovostní: '.$TotalCash[1].' Kč<br/>';
    $Output .= '  Bezhotovostní: '.$TotalCash[0].' Kč<br/>';
    return $Output;
  }

  function ShowExpenses(): string
  {
    $Table = array('Ne', 'Ano');
    $DbResult = $this->Database->select('FinanceYear', '*', 'Id='.$_GET['year']);
    $Year = $DbResult->fetch_assoc();

    $Total = 0;
    $TotalTax = array(0, 0);
    $TotalCash = array(0, 0);
    $Output = '<strong>Výdaje za rok '.$Year['Year'].'</strong>';
    $Output .= '<table style="font-size: smaller;" class="WideTable">';
    $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>';
    $DbResult = $this->Database->query('SELECT `FinanceOperation`.*, `DocumentLineCode`.`Name` AS `BillName`, '.
      '`Subject`.`Name` AS `SubjectName`, `FinanceOperationGroup`.`ValueSign` AS `ValueSign` FROM `FinanceOperation` '.
      'LEFT JOIN `Subject` ON `Subject`.`Id` = `FinanceOperation`.`Subject` '.
      'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id` = `FinanceOperation`.`Group` '.
      'LEFT JOIN `DocumentLineCode` ON `DocumentLineCode`.`Id` = `FinanceOperation`.`BillCode` '.
      'WHERE (`FinanceOperationGroup`.`ValueSign` = -1) AND (`FinanceOperation`.`Time` >= "'.$Year['DateStart'].'") '.
      'AND (`FinanceOperation`.`Time` <= "'.$Year['DateEnd'].'") ORDER BY `Time`');
    while ($Row = $DbResult->fetch_array())
    {
      $Row['Time'] = explode(' ', $Row['Time']);
      $Row['Time'] = $Row['Time'][0];
      $Row['Value'] = $Row['Value'] * $Row['ValueSign'];
      $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.$Row['BillName'].
      '</td><td>'.$Row['SubjectName'].'</td><td>'.$Row['Text'].'</td><td>'.$Row['Value'].
      '</td><td>'.$Table[$Row['Taxable']].'</td><td>'.$Table[$Row['Cash']].'</td></tr>';
      $Total += $Row['Value'];
      $TotalTax[$Row['Taxable']] += $Row['Value'];
      $TotalCash[$Row['Cash']] += $Row['Value'];
    }
    $Output .= '</table><br/>';
    $Output .= '<strong>Souhrn</strong><br/>';
    $Output .= 'Celkem: '.$Total.' Kč<br/>';
    $Output .= '  Daňové: '.$TotalTax[1].' Kč<br/>';
    $Output .= '  Nedaňové: '.$TotalTax[0].' Kč<br/>';
    $Output .= '  Hotovostní: '.$TotalCash[1].' Kč<br/>';
    $Output .= '  Bezhotovostní: '.$TotalCash[0].' Kč<br/>';
    return $Output;
  }

  function ShowClaims(): string
  {
    $Table = array('Ne', 'Ano');
    $DbResult = $this->Database->select('FinanceYear', '*', 'Id='.$_GET['year']);
    $Year = $DbResult->fetch_assoc();

    $Total = 0;
    $Output = '<strong>Pohledávky za rok '.$Year['Year'].'</strong>';
    $Output .= '<table style="font-size: smaller;" class="WideTable">';
    $Output .= '<tr><th>Čas vystavení</th><th>Kód</th><th>Subjekt</th><th>Text</th><th>Hodnota [Kč]</th></tr>';
    $DbResult = $this->Database->query('SELECT `FinanceInvoice`.*, '.
      '`DocumentLineCode`.`Name` AS `BillName`, `Subject`.`Name` AS `SubjectName`, '.
      '(SELECT GROUP_CONCAT(`Description` SEPARATOR ",") '.
      'FROM `FinanceInvoiceItem` WHERE `FinanceInvoiceItem`.`FinanceInvoice` = `FinanceInvoice`.`Id`) AS `Text` FROM `FinanceInvoice`'.
      'LEFT JOIN `Subject` ON `Subject`.`Id` = `FinanceInvoice`.`Subject` '.
      'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `FinanceInvoice`.`Group` '.
      'LEFT JOIN `DocumentLineCode` ON `DocumentLineCode`.`Id` = `FinanceInvoice`.`BillCode` '.
      'WHERE (`FinanceInvoiceGroup`.`ValueSign` = 1) AND (`FinanceInvoice`.`Time` >= "'.$Year['DateStart'].
      '") AND (`FinanceInvoice`.`Time` <= "'.$Year['DateEnd'].'") ORDER BY `Time`');
    while ($Row = $DbResult->fetch_array())
    {
      if ($Row['TimePayment'] == '0000-00-00 00:00:00') $Row['TimePayment'] = '&nbsp;';
      $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.$Row['BillName'].
      '</td><td>'.$Row['SubjectName'].'</td><td>'.$Row['Text'].'</td><td>'.$Row['Value'].'</td></tr>';
      $Total += $Row['Value'];
    }
    $Output .= '</table><br/>';
    $Output .= 'Celkem: '.$Total.' Kč<br/>';
    return $Output;
  }

  function ShowLiabilities(): string
  {
    $Table = array('Ne', 'Ano');
    $DbResult = $this->Database->select('FinanceYear', '*', 'Id='.$_GET['year']);
    $Year = $DbResult->fetch_assoc();

    $Total = 0;
    $Output = '<strong>Závazky za rok '.$Year['Year'].'</strong>';
    $Output .= '<table style="font-size: smaller;" class="WideTable">';
    $Output .= '<tr><th>Čas vystavení</th><th>Kód</th><th>Subjekt</th><th>Text</th><th>Hodnota [Kč]</th></tr>';
    $DbResult = $this->Database->query('SELECT *, `DocumentLineCode`.`Name` AS `BillName`, `Subject`.`Name` AS `SubjectName`, '.
      '(SELECT GROUP_CONCAT(Description SEPARATOR ",") '.
      'FROM `FinanceInvoiceItem` WHERE `FinanceInvoiceItem`.`FinanceInvoice` = `FinanceInvoice`.`Id`) AS `Text` '.
      'FROM `FinanceInvoice` '.
      'LEFT JOIN `Subject` ON `Subject`.`Id` = `FinanceInvoice`.`Subject` '.
      'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `FinanceInvoice`.`Group` '.
      'LEFT JOIN `DocumentLineCode` ON `DocumentLineCode`.`Id` = `FinanceInvoice`.`BillCode` '.
      'WHERE (`FinanceInvoiceGroup`.`ValueSign` = -1) AND (`FinanceInvoice`.`Time` >= "'.$Year['DateStart'].
      '") AND (FinanceInvoice.Time <= "'.$Year['DateEnd'].'") ORDER BY Time');
    while ($Row = $DbResult->fetch_array())
    {
      if ($Row['TimePayment'] == '0000-00-00 00:00:00') $Row['TimePayment'] = '&nbsp;';
      $Row['Value'] = $Row['Value'] * $Row['ValueSign'];
      $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.$Row['BillName'].
      '</td><td>'.$Row['SubjectName'].'</td><td>'.$Row['Text'].'</td><td>'.$Row['Value'].'</td></tr>';
      $Total += $Row['Value'];
    }
    $Output .= '</table><br/>';
    $Output .= 'Celkem: '.$Total.' Kč<br/>';
    return $Output;
  }

  function ShowSubjectList(): string
  {
    $Output = '<strong>Seznam subjektů</strong>';
    $Output .= '<table style="font-size: smaller;" class="WideTable">';
    $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>';
    $DbResult = $this->Database->query('SELECT Id, Name, 0 AS Cash, '.
      '(SELECT SUM(T1.Value) FROM FinanceInvoice AS T1 '.
      'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `T1`.`Group` '.
      'WHERE (T1.Subject = Subject.Id) AND (`FinanceInvoiceGroup`.ValueSign = 1)) AS `Claims`, '.
      '(SELECT SUM(T2.Value) FROM FinanceInvoice AS T2 '.
      'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `T2`.`Group` '.
      'WHERE (T2.Subject = Subject.Id) AND (`FinanceInvoiceGroup`.`ValueSign` = 1) AND (TimePayment IS NULL)) AS `OpenedClaims`, '.
      '(SELECT -SUM(T3.Value) FROM FinanceInvoice AS T3 '.
      'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `T3`.`Group` '.
      'WHERE (T3.Subject = Subject.Id) AND (`FinanceInvoiceGroup`.`ValueSign` = -1)) AS `Liabilities`, '.
      '(SELECT -SUM(T4.Value) FROM FinanceInvoice AS T4 '.
      'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `T4`.`Group` '.
      'WHERE (T4.Subject = Subject.Id) AND (`FinanceInvoiceGroup`.`ValueSign` = -1) AND (`TimePayment` IS NULL)) AS `OpenedLiabilities`, '.
      '(SELECT SUM(T5.Value) FROM FinanceOperation AS T5 '.
      'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id` = `T5`.`Group` '.
      'WHERE (T5.Subject = Subject.Id) AND (`FinanceOperationGroup`.`ValueSign` = 1)) AS `Gains`, '.
      '(SELECT SUM(T6.Value) FROM FinanceOperation AS T6 '.
      'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id` = `T6`.`Group` '.
      'WHERE (T6.Subject = Subject.Id) AND (`FinanceOperationGroup`.`ValueSign` = -1)) AS `Spends` '.
      'FROM Subject ORDER BY Name');
    while ($Row = $DbResult->fetch_assoc())
    {
      $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>';
    }
    $Output .= '</table>';
    return $Output;
  }

  function ShowSmallAssets(): string
  {
    $Output = '<strong>Drobný majetek</strong>';
    $Output .= '<table style="font-size: smaller;" border="1" cellspacing="0" cellpadding="3">';
    $Output .= '<tr><th>Název</th><th>Hodnota [Kč]</th><th>Datum zakoupení</th><th>Datum vyřezení</th></tr>';
    $DbResult = $this->Database->query('SELECT Product.Name AS Name, StockSerialNumber.SellPrice AS Price, StockSerialNumber.TimeEnlistment AS TimeEnlistment, StockSerialNumber.TimeElimination AS TimeElimination '.
      'FROM StockSerialNumber JOIN Product ON Product.Id = StockSerialNumber.Product '.
      'WHERE (TimeElimination IS NOT NULL)');
    while ($Row = $DbResult->fetch_array())
    {
      $Output .= '<tr><td>'.$Row['Name'].'</td><td>'.$Row['Price'].'</td><td>'.$Row['TimeEnlistment'].'</td><td>'.$Row['TimeElimination'].'</td></tr>';
    }
    $Output .= '</table>';
    return $Output;
  }

  function ShowSubjectAccount(): string
  {
    $Finance = &ModuleFinance::Cast($this->System->GetModule('Finance'))->Finance;

    $Output = '<table style="width: 100%"><tr><td style="vertical-align: top;">';
    $Output .= '<strong>Výpis příjmů/výdajů</strong>';
    $Output .= '<table style="font-size: smaller;" border="1" cellspacing="0" cellpadding="3">';
    $Output .= '<tr><th>Datum</th><th>Název</th><th>Hodnota [Kč]</th><th>Doklad</th></tr>';
    $DbResult = $this->Database->query('SELECT `FinanceOperation`.*, `DocumentLineCode`.`Name` AS `BillName` '.
      'FROM `FinanceOperation` '.
      'LEFT JOIN `DocumentLineCode` ON `DocumentLineCode`.`Id` = `FinanceOperation`.`BillCode` '.
      'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id` = `FinanceOperation`.`Group` '.
      'WHERE `Subject`='.$_GET['Id'].' ORDER BY `Time`');
    while ($Row = $DbResult->fetch_array())
    {
      $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.$Row['Text'].
      '</td><td>'.$Row['Value'].'</td><td>'.$Row['BillName'].'</td></tr>';
    }
    $Output .= '</table></td><td style="vertical-align: top;">';

    $Output .= '<strong>Výpis závazků/pohledávek</strong>';
    $Output .= '<table style="font-size: smaller;" border="1" cellspacing="0" cellpadding="3">';
    $Output .= '<tr><th>Datum vytvoření</th><th>Datum zaplacení</th><th>Název</th><th>Hodnota [Kč]</th><th>Doklad</th></tr>';
    $DbResult = $this->Database->query('SELECT `FinanceInvoice`.*, (SELECT GROUP_CONCAT(Description SEPARATOR ",") '.
      'FROM FinanceInvoiceItem WHERE `FinanceInvoiceItem`.`FinanceInvoice` = `FinanceInvoice`.`Id`) AS `Text`, `DocumentLineCode`.`Name` AS `BillName` '.
      'FROM `FinanceInvoice` '.
      'LEFT JOIN `DocumentLineCode` ON `DocumentLineCode`.`Id` = `FinanceInvoice`.`BillCode` '.
      'WHERE `Subject`='.$_GET['Id'].' ORDER BY `Time`');
    while ($Row = $DbResult->fetch_array())
    {
      $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.HumanDate($Row['TimePayment']).
      '</td><td>'.$Row['Text'].'</td><td>'.round($Row['Value'], $Finance->Rounding).'</td><td>'.$Row['BillName'].'</td></tr>';
    }
    $Output .= '</table></td></tr></table>';

    /*$Output .= '<strong>Výpis záloh</strong>';
     $Output .= '<table style="font-size: smaller;" border="1" cellspacing="0" cellpadding="3">';
     $Output .= '<tr><th>Datum vytvoření</th><th>Datum zaplacení</th><th>Název</th><th>Hodnota [Kč]</th></tr>';
     $DbResult = $this->Database->select('FinanceAdvances', '*', 'Subject='.$_GET['Id']);
     while ($Row = $DbResult->fetch_array())
     {
     $Output .= '<tr><td>'.$Row['Time'].'</td><td>'.$Row['TimePass'].'</td><td>'.$Row['ValueSign'].'</td><td>'.($Row['Value']).'</td><td>'.$Row['CashFlowId'].'</td></tr>';
     }
     $Output .= '</table>';*/

    $DbResult = $this->Database->query('SELECT Id, 0 AS Cash, (SELECT SUM(FinanceInvoice.Value * FinanceInvoice.ValueSign) '.
      'FROM FinanceInvoice WHERE (FinanceInvoice.Subject = Subject.Id) AND '.
      '(FinanceInvoice.ValueSign = 1)) as Claims, (SELECT SUM(FinanceInvoice.Value * FinanceInvoice.ValueSign) '.
      'FROM FinanceInvoice WHERE (FinanceInvoice.Subject = Subject.Id) AND ('.
      'FinanceInvoice.ValueSign = 1) AND (TimePayment IS NULL)) as OpenedClaims, '.
      '(SELECT SUM(FinanceInvoice.Value * FinanceInvoice.ValueSign) FROM FinanceInvoice '.
      'WHERE (FinanceInvoice.Subject = Subject.Id) AND (FinanceInvoice.ValueSign = -1)) AS '.
      'Liabilities, (SELECT SUM(FinanceInvoice.Value) FROM FinanceInvoice '.
      'WHERE (FinanceInvoice.Subject = Subject.Id) AND (FinanceInvoice.ValueSign = -1) '.
      'AND (TimePayment IS NULL)) AS OpenedLiabilities, '.
      '(SELECT SUM(FinanceOperation.Value*FinanceOperation.ValueSign) '.
      'FROM FinanceOperation WHERE FinanceOperation.Subject = Subject.Id AND FinanceOperation.ValueSign = 1) '.
      'AS Gains, (SELECT SUM(FinanceOperation.Value*FinanceOperation.ValueSign) FROM FinanceOperation WHERE '.
      'FinanceOperation.Subject = Subject.Id AND FinanceOperation.ValueSign = -1) as Spends '.
      'FROM Subject WHERE Id='.$_GET['Id']);
    $Row = $DbResult->fetch_array();
    $Output .= 'Stav placení: '.(-$Row['OpenedClaims'] + Abs($Row['OpenedLiabilities']));
    return $Output;
  }

  function ShowDefault(): string
  {
    $Output = '<strong>Celkové přehledy</strong><br/>';
    $Output .= '<a href="?table=AnnualBalance">Účetní závěrka</a><br />';
    $Output .= '<a href="?table=MonthlyBalance">Měsíční přehledy</a><br />';
    $Output .= '<a href="?table=SubjectList">Seznam subjektů</a><br />';
    $Output .= '<a href="?table=SmallAssets">Drobný majetek</a><br />';
    $Output .= '<br/>';
    $Output .= '<strong>Roční přehledy</strong><br/>';
    $Output .= $this->ShowFinanceYears();
    if (array_key_exists('year', $_GET))
    {
      $Year = $_GET['year'] * 1;
      $Output .= '<a href="?table=Incomes&amp;year='.$Year.'">Příjmy</a><br />';
      $Output .= '<a href="?table=Expenses&amp;year='.$Year.'">Výdaje</a><br />';
      $Output .= '<a href="?table=Claims&amp;year='.$Year.'">Pohledávky</a><br />';
      $Output .= '<a href="?table=Liabilities&amp;year='.$Year.'">Závazky</a><br />';
      $Output .= '<a href="?table=Advantages&amp;year='.$Year.'">Zálohy</a><br />';
      $Output .= '<a href="?table=YearReport&amp;year='.$Year.'">Souhrný přehled</a><br />';
    }
    return $Output;
  }

  function Show(): string
  {
    if (!ModuleUser::Cast($this->System->GetModule('User'))->User->CheckPermission('Finance', 'TradingStatus'))
      return 'Nemáte oprávnění';

    $Output = '';
    if (!array_key_exists('table', $_GET)) $_GET['table'] = '';
    switch ($_GET['table'])
    {
      case 'AnnualBalance':
        $Output = $this->ShowAnnualBalance();
        break;
      case 'MonthlyBalance':
        $Output = $this->ShowMonthlyBalance();
        break;
      case 'YearReport':
        $Output .= $this->ShowIncomes().'<hr>';
        $Output .= $this->ShowExpenses().'<hr>';
        $Output .= $this->ShowClaims().'<hr>';
        $Output .= $this->ShowLiabilities().'<hr>';
        break;
      case 'Incomes':
        $Output = $this->ShowIncomes();
        break;
      case 'Expenses':
        $Output = $this->ShowExpenses();
        break;
      case 'Claims':
        $Output = $this->ShowClaims();
        break;
      case 'Liabilities':
        $Output = $this->ShowLiabilities();
        break;
      case 'SubjectList':
        $Output = $this->ShowSubjectList();
        break;
      case 'SmallAssets':
        $Output = $this->ShowSmallAssets();
        break;
      case 'SubjectAccount':
        $Output = $this->ShowSubjectAccount();
        break;
      case 'PrintMonthOperations':
        $Output .= '<table><tr><th>Datum</th><th>Subjekt</th><td></td></tr></table>';
        break;
      case 'PrintMonthOperations':
        $Output .= 'Výpis operací od '.$_GET['TimeFrom'].' do '.$_GET['TimeTo'];
        $Output .= '<table><tr><th>Datum</th><th>Subjekt</th><th>Suma [Kč]</th><td></td></tr></table>';
        break;
      default:
        $Output = $this->ShowDefault();
    }
    return $Output;
  }

  function ShowFinanceYears(): string
  {
    $Output = 'Roky: ';
    $DbRows = $this->Database->select('FinanceYear', '*');
    while ($DbRow = $DbRows->fetch_assoc())
      $Output .= '<a href="?year='.$DbRow['Id'].'">'.$DbRow['Year'].'</a> ';
    $Output .= '<br/>';
    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ů
  }
}