<?php

include_once(dirname(__FILE__).'/FinanceModels.php');
include_once(dirname(__FILE__).'/Bill.php');
include_once(dirname(__FILE__).'/Manage.php');
include_once(dirname(__FILE__).'/UserState.php');
include_once(dirname(__FILE__).'/Import.php');
include_once(dirname(__FILE__).'/Trade.php');

class ModuleFinance extends Module
{
  public Finance $Finance;
  public Bill $Bill;

  function __construct(System $System)
  {
    parent::__construct($System);
    $this->Name = 'Finance';
    $this->Version = '1.0';
    $this->Creator = 'Chronos';
    $this->License = 'GNU/GPLv3';
    $this->Description = 'Base module for finance management';
    $this->Dependencies = array(ModuleFile::GetName(), ModuleEmailQueue::GetName(), ModuleSubject::GetName(),
      ModuleDocument::GetName());
    $this->Models = array(FinanceBillingPeriod::GetClassName(), FinanceVatType::GetClassName(), FinanceVat::GetClassName(),
      FinanceGroup::GetClassName(), FinanceOperationGroup::GetClassName(), FinanceOperation::GetClassName(),
      FinanceInvoiceGroup::GetClassName(), FinanceInvoice::GetClassName(), FinanceInvoiceItem::GetClassName(),
      Company::GetClassName(), FinanceTreasury::GetClassName(), FinanceTreasuryCheck::GetClassName(),
      Currency::GetClassName(), FinanceBank::GetClassName(), FinanceBankAccount::GetClassName(), FinanceCharge::GetClassName(),
      FinanceInvoiceOperationRel::GetClassName());

    $this->Bill = new Bill($this->System);
    $this->Finance = new Finance($this->System);
  }

  function DoStart(): void
  {
    $this->Finance->MainSubject = $this->System->Config['Finance']['MainSubjectId'];
    $this->Finance->DirectoryId = $this->System->Config['Finance']['DirectoryId'];

    $this->System->RegisterPage(['finance', 'sprava'], 'PageFinanceManage');
    $this->System->RegisterPage(['finance', 'platby'], 'PageFinanceUserState');
    $this->System->RegisterPage(['finance', 'import'], 'PageFinanceImportPayment');
    $this->System->RegisterPage(['finance', 'zivnost'], 'PageFinanceTaxFiling');    

    $this->System->FormManager->RegisterClass('FinanceOperation', array(
      'Title' => 'Finanční operace',
      'Table' => 'FinanceOperation',
      'DefaultSortColumn' => 'Time',
      'DefaultSortOrder' => 1,
      'Items' => array(
        'Group' => array('Type' => 'TFinanceOperationGroup', 'Caption' => 'Skupina', 'Default' => ''),
        'BillCode' => array('Type' => 'TDocumentLineCode', 'Caption' => 'Označení', 'Default' => '', 'ReadOnly' => true),
        'Subject' => array('Type' => 'TSubject', 'Caption' => 'Subjekt', 'Default' => ''),
        'Time' => array('Type' => 'DateTime', 'Caption' => 'Čas realizace', 'Default' => ''),
        'Cash' => array('Type' => 'Boolean', 'Caption' => 'Hotově', 'Default' => ''),
        'Taxable' => array('Type' => 'Boolean', 'Caption' => 'Zdanitelné', 'Default' => ''),
        'Value' => array('Type' => 'Integer', 'Caption' => 'Částka absolutní', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true),
        'ValueUser' => array('Type' => 'Integer', 'Caption' => 'Částka', 'Default' => '0', 'Suffix' => 'Kč'),
        'File' => array('Type' => 'TFile', 'Caption' => 'Doklad', 'Default' => '', 'Null' => true),
        'Text' => array('Type' => 'String', 'Caption' => 'Popis', 'Default' => ''),
        'Network' => array('Type' => 'Boolean', 'Caption' => 'Týkající sítě', 'Default' => ''),
        'BankAccount' => array('Type' => 'TFinanceBankAccount', 'Caption' => 'Účet', 'Default' => '', 'Null' => true),
        'Treasury' => array('Type' => 'TFinanceTreasury', 'Caption' => 'Pokladna', 'Default' => '', 'Null' => true),
        'Generate' => array('Type' => 'Boolean', 'Caption' => 'Generovat', 'Default' => ''),
        'Balance' => array('Type' => 'Integer', 'Caption' => 'Zůstatek', 'Default' => '',
          'ReadOnly' => true, 'SQL' => '(SELECT SUM(`FinanceOperation2`.`Value`) FROM `FinanceOperation` AS `FinanceOperation2` WHERE `FinanceOperation2`.`Time`<`Time`)'),
        'InvoiceRel' => array('Type' => 'TFinanceInvoiceOperationRelListOperation', 'Caption' => 'Zaplacené faktury', 'Default' => ''),
        'InvoiceRelCount' => array('Type' => 'Integer', 'Caption' => 'Faktur',
          'ReadOnly' => true, 'SQL' => '(SELECT COUNT(`FinanceInvoiceOperationRel`.`Id`) FROM `FinanceInvoiceOperationRel` '.
          'WHERE `FinanceInvoiceOperationRel`.`Operation`=#Id)'),
      ),
      'BeforeInsert' => array($this, 'BeforeInsertFinanceOperation'),
      'AfterInsert' => array($this, 'AfterInsertFinanceOperation'),
      'BeforeModify' => array($this, 'BeforeModifyFinanceOperation'),
      'AfterModify' => array($this, 'BeforeModifyFinanceOperation'),
      'ItemActions' => array(
        array('Caption' => 'Přegenerovat doklad', 'URL' => '/finance/sprava/?Operation=RegenerateOperation&i=#RowId'),
      ),
    ));

    $this->System->FormManager->RegisterClass('FinanceTreasuryIn', $this->System->FormManager->Classes['FinanceOperation']);
    $this->System->FormManager->Classes['FinanceTreasuryIn']['Title'] = 'Pokladní příjmy';
    $this->System->FormManager->Classes['FinanceTreasuryIn']['Items']['Group']['Default'] = OPERATION_GROUP_TREASURY_IN;
    $this->System->FormManager->Classes['FinanceTreasuryIn']['Items']['Group']['Hidden'] = true;
    $this->System->FormManager->Classes['FinanceTreasuryIn']['Items']['Group']['Filter'] = true;
    $this->System->FormManager->Classes['FinanceTreasuryIn']['Items']['Cash']['Default'] = 1;
    $this->System->FormManager->Classes['FinanceTreasuryIn']['Items']['Cash']['Hidden'] = true;
    $this->System->FormManager->Classes['FinanceTreasuryIn']['Items']['Cash']['Filter'] = false;
    $this->System->FormManager->Classes['FinanceTreasuryIn']['Items']['BankAccount']['Hidden'] = true;
    $this->System->FormManager->Classes['FinanceTreasuryIn']['Items']['Value']['Hidden'] = true;

    $this->System->FormManager->RegisterClass('FinanceTreasuryOut', $this->System->FormManager->Classes['FinanceOperation']);
    $this->System->FormManager->Classes['FinanceTreasuryOut']['Title'] = 'Pokladní výdeje';
    $this->System->FormManager->Classes['FinanceTreasuryOut']['Items']['Group']['Default'] = OPERATION_GROUP_TREASURY_OUT;
    $this->System->FormManager->Classes['FinanceTreasuryOut']['Items']['Group']['Hidden'] = true;
    $this->System->FormManager->Classes['FinanceTreasuryOut']['Items']['Group']['Filter'] = true;
    $this->System->FormManager->Classes['FinanceTreasuryIn']['Items']['Cash']['Default'] = 1;
    $this->System->FormManager->Classes['FinanceTreasuryIn']['Items']['Cash']['Hidden'] = true;
    $this->System->FormManager->Classes['FinanceTreasuryIn']['Items']['Cash']['Filter'] = false;
    $this->System->FormManager->Classes['FinanceTreasuryOut']['Items']['BankAccount']['Hidden'] = true;
    $this->System->FormManager->Classes['FinanceTreasuryOut']['Items']['Value']['Hidden'] = true;

    $this->System->FormManager->RegisterClass('FinanceAccountIn', $this->System->FormManager->Classes['FinanceOperation']);
    $this->System->FormManager->Classes['FinanceAccountIn']['Title'] = 'Příjmy na účet';
    $this->System->FormManager->Classes['FinanceAccountIn']['Items']['Group']['Default'] = OPERATION_GROUP_ACCOUNT_IN;
    $this->System->FormManager->Classes['FinanceAccountIn']['Items']['Group']['Hidden'] = true;
    $this->System->FormManager->Classes['FinanceAccountIn']['Items']['Group']['Filter'] = true;
    $this->System->FormManager->Classes['FinanceTreasuryIn']['Items']['Cash']['Default'] = 0;
    $this->System->FormManager->Classes['FinanceTreasuryIn']['Items']['Cash']['Hidden'] = true;
    $this->System->FormManager->Classes['FinanceTreasuryIn']['Items']['Cash']['Filter'] = false;
    $this->System->FormManager->Classes['FinanceAccountIn']['Items']['Treasury']['Hidden'] = true;
    $this->System->FormManager->Classes['FinanceAccountIn']['Items']['Value']['Hidden'] = true;

    $this->System->FormManager->RegisterClass('FinanceAccountOut', $this->System->FormManager->Classes['FinanceOperation']);
    $this->System->FormManager->Classes['FinanceAccountOut']['Title'] = 'Výdeje z účtu';
    $this->System->FormManager->Classes['FinanceAccountOut']['Items']['Group']['Default'] = OPERATION_GROUP_ACCOUNT_OUT;
    $this->System->FormManager->Classes['FinanceAccountOut']['Items']['Group']['Hidden'] = true;
    $this->System->FormManager->Classes['FinanceAccountOut']['Items']['Group']['Filter'] = true;
    $this->System->FormManager->Classes['FinanceTreasuryIn']['Items']['Cash']['Default'] = 0;
    $this->System->FormManager->Classes['FinanceTreasuryIn']['Items']['Cash']['Hidden'] = true;
    $this->System->FormManager->Classes['FinanceTreasuryIn']['Items']['Cash']['Filter'] = false;
    $this->System->FormManager->Classes['FinanceAccountOut']['Items']['Treasury']['Hidden'] = true;
    $this->System->FormManager->Classes['FinanceAccountOut']['Items']['Value']['Hidden'] = true;

    $this->System->FormManager->RegisterClass('FinanceOperationGroup', array(
      'Title' => 'Skupina finančních operací',
      'Table' => 'FinanceOperationGroup',
      'Items' => array(
        'Name' => array('Type' => 'String', 'Caption' => 'Název', 'Default' => '0'),
        'DocumentLine' => array('Type' => 'TDocumentLine', 'Caption' => 'Dokladová řada', 'Default' => '0'),
        'ValueSign' => array('Type' => 'TFinanceValueSign', 'Caption' => 'Znaménko hodnoty', 'Default' => '0'),
        'Direction' => array('Type' => 'TFinanceDirection', 'Caption' => 'Směr', 'Default' => '0'),
        'Items' => array('Type' => 'TFinanceOperationListGroup', 'Caption' => 'Operace', 'Default' => ''),
      ),
    ));
    $this->System->FormManager->RegisterClass('FinanceBillingPeriod', array(
      'Title' => 'Platební období',
      'Table' => 'FinanceBillingPeriod',
      'Items' => array(
        'Name' => array('Type' => 'String', 'Caption' => 'Název', 'Default' => '0'),
        'MonthCount' => array('Type' => 'Integer', 'Caption' => 'Počet měsíců', 'Default' => '0'),
        'Customers' => array('Type' => 'TCustomerBillingPeriod', 'Caption' => 'Zákazníci')
      ),
    ));
    $this->System->FormManager->RegisterFormType('TFinanceOperationGroup', array(
      'Type' => 'Reference',
      'Table' => 'FinanceOperationGroup',
      'Id' => 'Id',
      'Name' => 'Name',
      'Filter' => '1',
    ));
    $this->System->FormManager->RegisterFormType('TFinanceValueSign', array(
      'Type' => 'Enumeration',
      'States' => array(-1 => 'Mínus', 1 => 'Plus'),
    ));
    $this->System->FormManager->RegisterFormType('TFinanceDirection', array(
      'Type' => 'Enumeration',
      'States' => array(0 => 'Příjem', 1 => 'Výdej'),
    ));
    $this->System->FormManager->RegisterClass('FinanceInvoice', array(
      'Title' => 'Faktury',
      'Table' => 'FinanceInvoice',
      'DefaultSortColumn' => 'Time',
      'DefaultSortOrder' => 1,
      'Items' => array(
        'Group' => array('Type' => 'TFinanceInvoiceGroup', 'Caption' => 'Skupina', 'Default' => ''),
        'BillCode' => array('Type' => 'TDocumentLineCode', 'Caption' => 'Označení', 'Default' => '', 'ReadOnly' => true),
        'Subject' => array('Type' => 'TSubject', 'Caption' => 'Subjekt', 'Default' => ''),
        'Time' => array('Type' => 'Date', 'Caption' => 'Čas vytvoření', 'Default' => ''),
        'TimeDue' => array('Type' => 'Date', 'Caption' => 'Čas splatnosti', 'Default' => ''),
        'TimePayment' => array('Type' => 'Date', 'Caption' => 'Čas zaplacení', 'Default' => '', 'Null' => true),
        'Value' => array('Type' => 'Integer', 'Caption' => 'Částka absolutní', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true),
        'ValueUser' => array('Type' => 'Integer', 'Caption' => 'Částka', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => 'SELECT ROUND(SUM(`Price`*`Quantity`), '.$this->System->Config['Finance']['Rounding'].') FROM `FinanceInvoiceItem` WHERE `FinanceInvoiceItem`.`FinanceInvoice`=#Id'),
        'File' => array('Type' => 'TFile', 'Caption' => 'Doklad', 'Default' => '', 'Null' => true),
        'Generate' => array('Type' => 'Boolean', 'Caption' => 'Generovat', 'Default' => ''),
        'PeriodFrom' => array('Type' => 'Date', 'Caption' => 'Období od', 'Default' => '', 'Null' => true),
        'PeriodTo' => array('Type' => 'Date', 'Caption' => 'Období do', 'Default' => '', 'Null' => true),
        'Cash' => array('Type' => 'Boolean', 'Caption' => 'Platit hotově', 'Default' => ''),
        'VisibleToUser' => array('Type' => 'Boolean', 'Caption' => 'Viditelné uživatelům', 'Default' => '1'),
        'Items' => array('Type' => 'TFinanceInvoiceItemListInvoice', 'Caption' => 'Položky', 'Default' => ''),
        'StornoBy' => array('Type' => 'TFinanceInvoiceStornoListBy', 'Caption' => 'Původní doklady', 'Default' => ''),
        'StornoOf' => array('Type' => 'TFinanceInvoiceStornoListOf', 'Caption' => 'Storno doklady', 'Default' => ''),
        'OperationRel' => array('Type' => 'TFinanceInvoiceOperationRelListInvoice', 'Caption' => 'Platba', 'Default' => ''),
        'OperationRelCount' => array('Type' => 'Integer', 'Caption' => 'Plateb',
          'ReadOnly' => true, 'SQL' => '(SELECT COUNT(`FinanceInvoiceOperationRel`.`Id`) FROM `FinanceInvoiceOperationRel` '.
          'WHERE `FinanceInvoiceOperationRel`.`Invoice`=#Id)'),
      ),
      'BeforeInsert' => array($this, 'BeforeInsertFinanceInvoice'),
      'AfterInsert' => array($this, 'AfterInsertFinanceInvoice'),
      'BeforeModify' => array($this, 'BeforeModifyFinanceInvoice'),
      'AfterModify' => array($this, 'BeforeModifyFinanceInvoice'),
      'ItemActions' => array(
        array('Caption' => 'Přegenerovat doklad', 'URL' => '/finance/sprava/?Operation=RegenerateInvoice&i=#RowId'),
      ),
    ));
    $this->System->FormManager->RegisterClass('FinanceInvoiceIn', $this->System->FormManager->Classes['FinanceInvoice']);
    $this->System->FormManager->Classes['FinanceInvoiceIn']['Title'] = 'Přijaté faktury';
    $this->System->FormManager->Classes['FinanceInvoiceIn']['Items']['Group']['Default'] = INVOICE_GROUP_IN;
    $this->System->FormManager->Classes['FinanceInvoiceIn']['Items']['Group']['Hidden'] = true;
    $this->System->FormManager->Classes['FinanceInvoiceIn']['Items']['Group']['Filter'] = true;
    $this->System->FormManager->Classes['FinanceInvoiceIn']['Items']['Value']['Hidden'] = true;

    $this->System->FormManager->RegisterClass('FinanceInvoiceOut', $this->System->FormManager->Classes['FinanceInvoice']);
    $this->System->FormManager->Classes['FinanceInvoiceOut']['Title'] = 'Vydané faktury';
    $this->System->FormManager->Classes['FinanceInvoiceOut']['Items']['Group']['Default'] = INVOICE_GROUP_OUT;
    $this->System->FormManager->Classes['FinanceInvoiceOut']['Items']['Group']['Hidden'] = true;
    $this->System->FormManager->Classes['FinanceInvoiceOut']['Items']['Group']['Filter'] = true;
    $this->System->FormManager->Classes['FinanceInvoiceOut']['Items']['Value']['Hidden'] = true;

    $this->System->FormManager->RegisterClass('FinanceInvoiceGroup', array(
      'Title' => 'Skupina faktur',
      'Table' => 'FinanceInvoiceGroup',
      'Items' => array(
        'Name' => array('Type' => 'String', 'Caption' => 'Název', 'Default' => '0'),
        'DocumentLine' => array('Type' => 'TDocumentLine', 'Caption' => 'Dokladová řada', 'Default' => '0'),
        'ValueSign' => array('Type' => 'TFinanceValueSign', 'Caption' => 'Znaménko hodnoty', 'Default' => '0'),
        'Direction' => array('Type' => 'TFinanceDirection', 'Caption' => 'Směr', 'Default' => '0'),
        'Items' => array('Type' => 'TFinanceInvoiceListGroup', 'Caption' => 'Faktury', 'Default' => ''),
      ),
    ));

    $this->System->FormManager->RegisterClass('FinanceInvoiceStorno', array(
      'Title' => 'Storno faktur',
      'Table' => 'FinanceInvoiceStorno',
      'Items' => array(
        'StornoBy' => array('Type' => 'TFinanceInvoice', 'Caption' => 'Storno doklad', 'Default' => ''),
        'StornoOf' => array('Type' => 'TFinanceInvoice', 'Caption' => 'Původní doklad', 'Default' => ''),
      ),
    ));
    $this->System->FormManager->RegisterFormType('TFinanceInvoiceGroup', array(
        'Type' => 'Reference',
        'Table' => 'FinanceInvoiceGroup',
        'Id' => 'Id',
        'Name' => 'Name',
        'Filter' => '1',
    ));

    $this->System->FormManager->RegisterClass('Company', array(
      'Title' => 'Firma',
      'Table' => 'Company',
      'Items' => array(
        'Name' => array('Type' => 'String', 'Caption' => 'Název', 'Default' => '0'),
        'Subject' => array('Type' => 'TSubject', 'Caption' => 'Subjekt', 'Default' => '0'),
      ),
    ));
    $this->System->FormManager->RegisterClass('FinanceInvoiceItem', array(
      'Title' => 'Položka faktury',
      'Table' => 'FinanceInvoiceItem',
      'Items' => array(
        'FinanceInvoice' => array('Type' => 'TFinanceInvoice', 'Caption' => 'Faktura', 'Default' => '0'),
        'Description' => array('Type' => 'String', 'Caption' => 'Popis', 'Default' => 'Položka'),
        'Price' => array('Type' => 'Float', 'Caption' => 'Částka', 'Default' => '0', 'Suffix' => 'Kč'),
        'Quantity' => array('Type' => 'Float', 'Caption' => 'Množství', 'Default' => '1'),
        'VAT' => array('Type' => 'Integer', 'Caption' => 'Daň', 'Default' => '21', 'Suffix' => '%'),
        'Total' => array('Type' => 'Integer', 'Caption' => 'Celkem', 'Default' => '', 'Suffix' => 'Kč',
          'ReadOnly' => true, 'SQL' => 'ROUND(`Price` * `Quantity`, '.$this->System->Config['Finance']['Rounding'].')'),
      ),
      'AfterInsert' => array($this, 'AfterInsertFinanceInvoiceItem'),
      'AfterModify' => array($this, 'AfterModifyFinanceInvoiceItem'),
      'AfterDelete' => array($this, 'AfterModifyFinanceInvoiceItem'),
    ));
    $this->System->FormManager->RegisterClass('FinanceTreasury', array(
      'Title' => 'Pokladny',
      'Table' => 'FinanceTreasury',
      'DefaultSortColumn' => 'Name',
      'Items' => array(
        'Name' => array('Type' => 'String', 'Caption' => 'Jméno', 'Default' => ''),
        'TimeCreate' => array('Type' => 'Date', 'Caption' => 'Čas vytvoření', 'Default' => ''),
        'State' => array('Type' => 'Float', 'Caption' => 'Stav', 'Default' => '',
          'ReadOnly' => true, 'Suffix' => 'Kč', 'SQL' => 'IFNULL(ROUND((SELECT SUM(`FinanceOperation`.`Value`) FROM `FinanceOperation` '.
          'WHERE `FinanceOperation`.`Treasury`=#Id), '.$this->System->Config['Finance']['Rounding'].'), 0)'),
        'Operations' => array('Type' => 'TFinanceOperationListTreasury', 'Caption' => 'Operace', 'Default' => ''),
        'Check' => array('Type' => 'TFinanceTreasuryCheckListTreasury', 'Caption' => 'Kontrola', 'Default' => ''),
      ),
    ));
    $this->System->FormManager->RegisterClass('FinanceTreasuryCheck', array(
      'Title' => 'Kontrola pokladen',
      'Table' => 'FinanceTreasuryCheck',
      'DefaultSortColumn' => 'Time',
      'DefaultSortOrder' => 1,
      'Items' => array(
        'Treasury' => array('Type' => 'TFinanceTreasury', 'Caption' => 'Pokladna', 'Default' => ''),
        'Time' => array('Type' => 'DateTime', 'Caption' => 'Čas', 'Default' => ''),
        'Value1' => array('Type' => 'Integer', 'Caption' => 'Hodnota 1', 'Default' => '0', 'Suffix' => 'ks'),
        'Value2' => array('Type' => 'Integer', 'Caption' => 'Hodnota 2', 'Default' => '0', 'Suffix' => 'ks'),
        'Value5' => array('Type' => 'Integer', 'Caption' => 'Hodnota 5', 'Default' => '0', 'Suffix' => 'ks'),
        'Value10' => array('Type' => 'Integer', 'Caption' => 'Hodnota 10', 'Default' => '0', 'Suffix' => 'ks'),
        'Value20' => array('Type' => 'Integer', 'Caption' => 'Hodnota 20', 'Default' => '0', 'Suffix' => 'ks'),
        'Value50' => array('Type' => 'Integer', 'Caption' => 'Hodnota 50', 'Default' => '0', 'Suffix' => 'ks'),
        'Value100' => array('Type' => 'Integer', 'Caption' => 'Hodnota 100', 'Default' => '0', 'Suffix' => 'ks'),
        'Value200' => array('Type' => 'Integer', 'Caption' => 'Hodnota 200', 'Default' => '0', 'Suffix' => 'ks'),
        'Value500' => array('Type' => 'Integer', 'Caption' => 'Hodnota 500', 'Default' => '0', 'Suffix' => 'ks'),
        'Value1000' => array('Type' => 'Integer', 'Caption' => 'Hodnota 1000', 'Default' => '0', 'Suffix' => 'ks'),
        'Value2000' => array('Type' => 'Integer', 'Caption' => 'Hodnota 2000', 'Default' => '0', 'Suffix' => 'ks'),
        'Value5000' => array('Type' => 'Integer', 'Caption' => 'Hodnota 5000', 'Default' => '0', 'Suffix' => 'ks'),
        'Sum' => array('Type' => 'Integer', 'Caption' => 'Součet', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(`Value1` * 1 + `Value2` * 2 + `Value5` * 5 + `Value10` * 10 + '.
          '`Value20` * 20 + `Value50` * 50 + `Value100` * 100 + `Value200` * 200 + '.
          '`Value500` * 500 + `Value1000` * 1000 + `Value2000` * 2000 + `Value5000` * 5000)'),
        'Missing' => array('Type' => 'Integer', 'Caption' => 'Odchylka', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => 
         '-(`Value1` * 1 + `Value2` * 2 + `Value5` * 5 + `Value10` * 10 + '.
          '`Value20` * 20 + `Value50` * 50 + `Value100` * 100 + `Value200` * 200 + '.
          '`Value500` * 500 + `Value1000` * 1000 + `Value2000` * 2000 + `Value5000` * 5000) + '.
          '(SELECT (T2.`Value1` * 1 + T2.`Value2` * 2 + T2.`Value5` * 5 + T2.`Value10` * 10 + '.
          'T2.`Value20` * 20 + T2.`Value50` * 50 + T2.`Value100` * 100 + T2.`Value200` * 200 + '.
          'T2.`Value500` * 500 + T2.`Value1000` * 1000 + T2.`Value2000` * 2000 + T2.`Value5000` * 5000)'.
          ' + (SELECT SUM(T3.Value) FROM FinanceOperation AS T3 WHERE (T3.Time <= TX.Time) AND (T3.Time > T2.Time) AND (T3.Treasury=TX.Treasury))'.
          ' FROM FinanceTreasuryCheck AS T2 WHERE (T2.Time < TX.Time) ORDER BY T2.Time DESC LIMIT 1)'),
      ),
    ));
    $this->System->FormManager->RegisterFormType('TCustomerBillingPeriod', array(
      'Type' => 'ManyToOne',
      'Table' => 'Member',
      'Id' => 'Id',
      'Ref' => 'BillingPeriod',
      'Filter' => '1',
    ));
    $this->System->FormManager->RegisterFormType('TFinanceTreasuryCheckListTreasury', array(
      'Type' => 'ManyToOne',
      'Table' => 'FinanceTreasuryCheck',
      'Id' => 'Id',
      'Ref' => 'Treasury',
      'Filter' => '1',
    ));
    $this->System->FormManager->RegisterFormType('TFinanceTreasury', array(
      'Type' => 'Reference',
      'Table' => 'FinanceTreasury',
      'Id' => 'Id',
      'Name' => 'Name',
      'Filter' => '1',
    ));
    $this->System->FormManager->RegisterClass('FinanceBankAccount', array(
      'Title' => 'Účty',
      'Table' => 'FinanceBankAccount',
      'DefaultSortColumn' => 'Comment',
      'Items' => array(
        'Subject' => array('Type' => 'TSubject', 'Caption' => 'Vlastník', 'Default' => ''),
        'Comment' => array('Type' => 'String', 'Caption' => 'Komentář', 'Default' => ''),
        'Number' => array('Type' => 'String', 'Caption' => 'Číslo', 'Default' => ''),
        'Bank' => array('Type' => 'TFinanceBank', 'Caption' => 'Banka', 'Default' => ''),
        'TimeCreate' => array('Type' => 'Date', 'Caption' => 'Čas vytvoření', 'Default' => ''),
        'TimeEnd' => array('Type' => 'Date', 'Caption' => 'Čas zrušení', 'Default' => '', 'Null' => true),
        'Currency' => array('Type' => 'TCurrency', 'Caption' => 'Měna', 'Default' => ''),
        'LoginName' => array('Type' => 'String', 'Caption' => 'Přihlašovací jméno / token', 'Default' => '', 'NotInList' => true),
        'LoginPassword' => array('Type' => 'String', 'Caption' => 'Přihlašovací heslo', 'Default' => '', 'NotInList' => true),
        'Operations' => array('Type' => 'TFinanceOperationListAccount', 'Caption' => 'Operace', 'Default' => ''),
        'Use' => array('Type' => 'Boolean', 'Caption' => 'Používat', 'Default' => '0'),
        'LastImportDate' => array('Type' => 'Date', 'Caption' => 'Datum posledního importu', 'Default' => ''),
        'LastImportId' => array('Type' => 'String', 'Caption' => 'Id posledního importu', 'Default' => ''),
        'State' => array('Type' => 'Float', 'Caption' => 'Stav', 'Default' => '',
          'ReadOnly' => true, 'Suffix' => 'Kč', 'SQL' => '(SELECT SUM(`FinanceOperation`.`Value`) FROM `FinanceOperation` '.
          'WHERE `FinanceOperation`.`BankAccount`=#Id)'),
        'AutoImport' => array('Type' => 'Boolean', 'Caption' => 'Automaticky stahovat z banky', 'Default' => ''),
      ),
      'ItemActions' => array(
        array('Caption' => 'Import plateb z banky', 'URL' => '/finance/import-api/?i=#RowId'),
        array('Caption' => 'Import plateb ze souboru', 'URL' => '/finance/import-soubor/?i=#RowId'),
      ),
    ));
    $this->System->FormManager->RegisterClass('FinanceBankAccountReport', array(
      'Title' => 'Roční výkaz operací',
      'Table' => 'FinanceBankAccountReport',
      'SQL' => 'SELECT Id, DateEnd, DateStart FROM FinanceYear',
      'DefaultSortColumn' => 'Id',
      'Items' => array(
        'Id' => array('Type' => 'TFinanceYear', 'Caption' => 'Rok', 'Default' => '',
          'ReadOnly' => true),
        //'Account' => array('Type' => 'TFinanceBankAccount', 'Caption' => 'Bankovní účet', 'Default' => '',
        //  'ReadOnly' => true),
        'StartBalance' => array('Type' => 'Integer', 'Caption' => 'Starý zůstatek', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
          'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id`=`FinanceOperation`.`Group` '.
          'WHERE (`FinanceOperation`.`Time` <= `DateStart`) AND (`FinanceOperation`.`BankAccount` = 2))'),
        'EndBalance' => array('Type' => 'Integer', 'Caption' => 'Nový zůstatek', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
          'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id`=`FinanceOperation`.`Group` '.
          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) AND (`FinanceOperation`.`BankAccount` = 2))'),
        'Change' => array('Type' => 'Integer', 'Caption' => 'Změna', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
          'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id`=`FinanceOperation`.`Group` '.
          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
          'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperation`.`BankAccount` = 2))'),
      ),
      //'AfterInsert' => array($this, 'AfterInsertFinanceYear'),
    ));
    $this->System->FormManager->RegisterFormType('TFinanceBankAccount', array(
      'Type' => 'Reference',
      'Table' => 'FinanceBankAccount',
      'Id' => 'Id',
      'Name' => 'Comment',
      'Filter' => '1',
    ));
    $this->System->FormManager->RegisterClass('FinanceBank', array(
      'Title' => 'Banky',
      'Table' => 'FinanceBank',
      'Items' => array(
        'Name' => array('Type' => 'String', 'Caption' => 'Název', 'Default' => ''),
        'Code' => array('Type' => 'String', 'Caption' => 'Český kód', 'Default' => ''),
        'BIC' => array('Type' => 'String', 'Caption' => 'Kód BIC', 'Default' => ''),
        'Country' => array('Type' => 'TCountry', 'Caption' => 'Země', 'Default' => ''),
      ),
    ));
    $this->System->FormManager->RegisterFormType('TFinanceBank', array(
      'Type' => 'Reference',
      'Table' => 'FinanceBank',
      'Id' => 'Id',
      'Name' => 'CONCAT(Name, " (", Code, ")")',
      'Filter' => '1',
    ));
    $this->System->FormManager->RegisterClass('Currency', array(
      'Title' => 'Měny',
      'Table' => 'Currency',
      'Items' => array(
        'Code' => array('Type' => 'String', 'Caption' => 'Kód', 'Default' => ''),
        'Name' => array('Type' => 'String', 'Caption' => 'Jméno', 'Default' => ''),
        'Symbol' => array('Type' => 'String', 'Caption' => 'Symbol', 'Default' => ''),
      ),
    ));
    $this->System->FormManager->RegisterClass('FinanceCharge', array(
      'Title' => 'Parametry účtování',
      'Table' => 'FinanceCharge',
      'Items' => array(
        'Internet' => array('Type' => 'Integer', 'Caption' => 'Platba Internetu', 'Default' => '0', 'Suffix' => 'Kč'),
        'InternetSpeed' => array('Type' => 'Integer', 'Caption' => 'Rychlost Internetu', 'Default' => '0', 'Suffix' => 'Mbit/s'),
        'InternetSpeedReserve' => array('Type' => 'Integer', 'Caption' => 'Rezerva rychlosti', 'Default' => '0', 'Suffix' => 'Mbit/s'),
        'AdministrationPerUser' => array('Type' => 'Integer', 'Caption' => 'Správa za uživatele', 'Default' => '0', 'Suffix' => 'Kč'),
        'kWh' => array('Type' => 'Integer', 'Caption' => 'Cena kWh', 'Default' => '0', 'Suffix' => 'Kč'),
        'BaseSpeedElement' => array('Type' => 'Integer', 'Caption' => 'Základní díl rychlosti', 'Default' => '0', 'Suffix' => 'Mbit/s'),
        'BaseTariffPrice' => array('Type' => 'Integer', 'Caption' => 'Základní cena tarifu', 'Default' => '0', 'Suffix' => 'Kč'),
        'TopTariffPrice' => array('Type' => 'Integer', 'Caption' => 'Nejvyšší cena tarifu', 'Default' => '0', 'Suffix' => 'Kč'),
        'ChangeAction' => array('Type' => 'TActionEnum', 'Caption' => 'Změna - akce', 'Default' => '', 'Null' => true, 'NotInList' => true),
        'ChangeTime' => array('Type' => 'DateTime', 'Caption' => 'Změna - čas', 'Default' => '', 'Null' => true, 'NotInList' => true),
        'ChangeReplaceId' => array('Type' => 'TFinanceCharge', 'Caption' => 'Změna - položka', 'Default' => '0', 'Null' => true, 'NotInList' => true),
      ),
    ));
    $this->System->FormManager->RegisterClass('FinanceVAT', array(
      'Title' => 'Sazby DPH',
      'Table' => 'FinanceVAT',
      'Items' => array(
        'Type' => array('Type' => 'TFinanceVATType', 'Caption' => 'Typ', 'Default' => ''),
        'ValidFrom' => array('Type' => 'Date', 'Caption' => 'Platnost od', 'Default' => ''),
        'ValidTo' => array('Type' => 'Date', 'Caption' => 'Platnost do', 'Default' => '', 'Null' => true),
        'Value' => array('Type' => 'Integer', 'Caption' => 'Hodnota', 'Default' => '', 'Suffix' => '%'),
      ),
    ));
    $this->System->FormManager->RegisterClass('FinanceVATType', array(
      'Title' => 'Sazby DPH',
      'Table' => 'FinanceVATType',
      'Items' => array(
        'Name' => array('Type' => 'String', 'Caption' => 'Název', 'Default' => ''),
      ),
    ));
    $this->System->FormManager->RegisterFormType('TFinanceVAT', array(
      'Type' => 'Reference',
      'Table' => 'FinanceVAT',
      'Id' => 'Id',
      'Name' => 'Name',
      'Filter' => '1',
    ));
    $this->System->FormManager->RegisterFormType('TFinanceVATType', array(
      'Type' => 'Reference',
      'Table' => 'FinanceVATType',
      'Id' => 'Id',
      'Name' => 'Name',
      'Filter' => '1',
    ));
    $this->System->FormManager->RegisterFormType('TBankAccount', array(
      'Type' => 'Reference',
      'Table' => 'FinanceBankAccount',
      'Id' => 'Id',
      'Name' => 'CONCAT(`Comment`, " (", `Number`, "/", '.
      '(SELECT `FinanceBank`.`Code` FROM `FinanceBank` WHERE `FinanceBank`.`Id`=`FinanceBankAccount`.`Bank`), ")")',
      'Filter' => '1',
    ));
    $this->System->FormManager->RegisterClass('FinanceYearReport', array(
      'Title' => 'Roční výkaz',
      'Table' => 'FinanceYearReport',
      'SQL' => 'SELECT Id, DateEnd, DateStart FROM FinanceYear',
      'DefaultSortColumn' => 'Id',
      'DefaultSortOrder' => 1,
      'Items' => array(
        'Id' => array('Type' => 'TFinanceYear', 'Caption' => 'Rok', 'Default' => '',
          'ReadOnly' => true),
        'Income' => array('Type' => 'Integer', 'Caption' => 'Evidenční příjmy', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
          'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id`=`FinanceOperation`.`Group` '.
          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
          'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperationGroup`.`ValueSign` = 1))'),
        'Spending' => array('Type' => 'Integer', 'Caption' => 'Evidenční výdaje', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT -ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
          'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id`=`FinanceOperation`.`Group` '.
          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
          'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperationGroup`.`ValueSign` = -1))'),
        'OperationBalance' => array('Type' => 'Integer', 'Caption' => 'Evidenční zisk', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
          'AND (`FinanceOperation`.`Time` >= `DateStart`))'),
        'RealIncome' => array('Type' => 'Integer', 'Caption' => 'Skutečné příjmy', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
          'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id`=`FinanceOperation`.`Group` '.
          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) AND (`FinanceOperation`.`Subject` != '.$this->Finance->MainSubject.')'.
          'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperationGroup`.`ValueSign` = 1))'),
        'RealSpending' => array('Type' => 'Integer', 'Caption' => 'Skutečné výdaje', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT -ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
          'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id`=`FinanceOperation`.`Group` '.
          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) AND (`FinanceOperation`.`Subject` != '.$this->Finance->MainSubject.')'.
          'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperationGroup`.`ValueSign` = -1))'),
        'RealOperationBalance' => array('Type' => 'Integer', 'Caption' => 'Skutečný zisk', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) AND (`FinanceOperation`.`Subject` != '.$this->Finance->MainSubject.')'.
          'AND (`FinanceOperation`.`Time` >= `DateStart`))'),
        'TaxIncome' => array('Type' => 'Integer', 'Caption' => 'Daňové příjmy', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
          'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id`=`FinanceOperation`.`Group` '.
          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
          'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperation`.`Taxable` = 1) AND (`FinanceOperationGroup`.`ValueSign` = 1))'),
        'TaxSpending' => array('Type' => 'Integer', 'Caption' => 'Daňové výdaje', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT -ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
          'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id`=`FinanceOperation`.`Group` '.
          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
          'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperation`.`Taxable` = 1) AND (`FinanceOperationGroup`.`ValueSign` = -1))'),
        'TaxBase' => array('Type' => 'Integer', 'Caption' => 'Základ daně', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
          'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperation`.`Taxable` = 1))'),
        'Claims' => array('Type' => 'Integer', 'Caption' => 'Pohledávky', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT ROUND(SUM(`FinanceInvoice`.`Value`)) FROM `FinanceInvoice` '.
          'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id`=`FinanceInvoice`.`Group` '.
          'WHERE (`FinanceInvoice`.`Time` <= `DateEnd`) '.
          'AND (`FinanceInvoice`.`Time` >= `DateStart`) AND (`FinanceInvoiceGroup`.`ValueSign` = 1))'),
        'Liabilities' => array('Type' => 'Integer', 'Caption' => 'Závazky', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT -ROUND(SUM(`FinanceInvoice`.`Value`)) FROM `FinanceInvoice` '.
          'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id`=`FinanceInvoice`.`Group` '.
          'WHERE (`FinanceInvoice`.`Time` <= `DateEnd`) '.
          'AND (`FinanceInvoice`.`Time` >= `DateStart`) AND (`FinanceInvoiceGroup`.`ValueSign` = -1))'),
        'InvoiceBalance' => array('Type' => 'Integer', 'Caption' => 'Fakturační rozdíl', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT ROUND(SUM(`FinanceInvoice`.`Value`)) FROM `FinanceInvoice` WHERE (`FinanceInvoice`.`Time` <= `DateEnd`) '.
          'AND (`FinanceInvoice`.`Time` >= `DateStart`))'),
        'IncomeList' => array('Type' => 'TIncomeList', 'Caption' => 'Příjmy', 'Default' => '0'),
        'SpendingList' => array('Type' => 'TSpendingList', 'Caption' => 'Výdaje', 'Default' => '0'),
        'TaxIncomeList' => array('Type' => 'TTaxIncomeList', 'Caption' => 'Daňové příjmy', 'Default' => '0'),
        'TaxSpendingList' => array('Type' => 'TTaxSpendingList', 'Caption' => 'Daňové výdaje', 'Default' => '0'),
        'ClaimsList' => array('Type' => 'TClaimsList', 'Caption' => 'Pohledávky', 'Default' => '0'),
        'LiabilitiesList' => array('Type' => 'TLiabilitiesList', 'Caption' => 'Závazky', 'Default' => '0'),
      ),
      //'AfterInsert' => array($this, 'AfterInsertFinanceYear'),
    ));
    $this->System->FormManager->RegisterClass('FinanceMonthReport', array(
      'Title' => 'Měsíční výkaz',
      'Table' => 'FinanceMonthReport',
      'SQL' => 'SELECT 0 AS `Id`, m1 AS `DateStart`, LAST_DAY(m1) AS `DateEnd` FROM '.
      '(SELECT ((SELECT MIN(`DateStart`) FROM `FinanceYear`) - INTERVAL DAYOFMONTH((SELECT MIN(`DateStart`) FROM `FinanceYear`)) - 1 DAY) '.
      '+ INTERVAL m MONTH AS m1 FROM ('.
      'SELECT @rownum := @rownum + 1 AS m FROM '.
      '(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t1,'.
      '(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2,'.
      '(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t3,'.
      '(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t4,'.
      '(SELECT @rownum := -1) t0'.
      ') d1'.
      ') d2 '.
      'WHERE m1 <= (SELECT MAX(`DateEnd`) FROM `FinanceYear`)',
      'DefaultSortColumn' => 'DateStart',
      'DefaultSortOrder' => 1,
      'Items' => array(
        'DateStart' => array('Type' => 'Date', 'Caption' => 'Měsíc', 'Default' => '', 'ReadOnly' => true),
        'Income' => array('Type' => 'Integer', 'Caption' => 'Evidenční příjmy', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
          'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id`=`FinanceOperation`.`Group` '.
          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
          'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperationGroup`.`ValueSign` = 1))'),      
        'Spending' => array('Type' => 'Integer', 'Caption' => 'Evidenční výdaje', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT -ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
          'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id`=`FinanceOperation`.`Group` '.
          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
          'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperationGroup`.`ValueSign` = -1))'),
        'OperationBalance' => array('Type' => 'Integer', 'Caption' => 'Evidenční zisk', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
          'AND (`FinanceOperation`.`Time` >= `DateStart`))'),
        'RealIncome' => array('Type' => 'Integer', 'Caption' => 'Skutečné příjmy', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
          'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id`=`FinanceOperation`.`Group` '.
          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) AND (`FinanceOperation`.`Subject` != '.$this->Finance->MainSubject.')'.
          'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperationGroup`.`ValueSign` = 1))'),
        'RealSpending' => array('Type' => 'Integer', 'Caption' => 'Skutečné výdaje', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT -ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
          'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id`=`FinanceOperation`.`Group` '.
          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) AND (`FinanceOperation`.`Subject` != '.$this->Finance->MainSubject.')'.
          'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperationGroup`.`ValueSign` = -1))'),
        'RealOperationBalance' => array('Type' => 'Integer', 'Caption' => 'Skutečný zisk', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) AND (`FinanceOperation`.`Subject` != '.$this->Finance->MainSubject.')'.
          'AND (`FinanceOperation`.`Time` >= `DateStart`))'),
        'TaxIncome' => array('Type' => 'Integer', 'Caption' => 'Daňové příjmy', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
          'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id`=`FinanceOperation`.`Group` '.
          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
          'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperation`.`Taxable` = 1) AND (`FinanceOperationGroup`.`ValueSign` = 1))'),
        'TaxSpending' => array('Type' => 'Integer', 'Caption' => 'Daňové výdaje', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT -ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
          'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id`=`FinanceOperation`.`Group` '.
          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
          'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperation`.`Taxable` = 1) AND (`FinanceOperationGroup`.`ValueSign` = -1))'),
        'TaxBase' => array('Type' => 'Integer', 'Caption' => 'Základ daně', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
          'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperation`.`Taxable` = 1))'),
        'Claims' => array('Type' => 'Integer', 'Caption' => 'Pohledávky', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT ROUND(SUM(`FinanceInvoice`.`Value`)) FROM `FinanceInvoice` '.
          'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id`=`FinanceInvoice`.`Group` '.
          'WHERE (`FinanceInvoice`.`Time` <= `DateEnd`) '.
          'AND (`FinanceInvoice`.`Time` >= `DateStart`) AND (`FinanceInvoiceGroup`.`ValueSign` = 1))'),
        'Liabilities' => array('Type' => 'Integer', 'Caption' => 'Závazky', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT -ROUND(SUM(`FinanceInvoice`.`Value`)) FROM `FinanceInvoice` '.
          'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id`=`FinanceInvoice`.`Group` '.
          'WHERE (`FinanceInvoice`.`Time` <= `DateEnd`) '.
          'AND (`FinanceInvoice`.`Time` >= `DateStart`) AND (`FinanceInvoiceGroup`.`ValueSign` = -1))'),
        'InvoiceBalance' => array('Type' => 'Integer', 'Caption' => 'Fakturační rozdíl', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
          'SQL' => '(SELECT ROUND(SUM(`FinanceInvoice`.`Value`)) FROM `FinanceInvoice` WHERE (`FinanceInvoice`.`Time` <= `DateEnd`) '.
          'AND (`FinanceInvoice`.`Time` >= `DateStart`))'),
        'IncomeList' => array('Type' => 'TIncomeList', 'Caption' => 'Příjmy', 'Default' => '0'),
        'SpendingList' => array('Type' => 'TSpendingList', 'Caption' => 'Výdaje', 'Default' => '0'),
        'TaxIncomeList' => array('Type' => 'TTaxIncomeList', 'Caption' => 'Daňové příjmy', 'Default' => '0'),
        'TaxSpendingList' => array('Type' => 'TTaxSpendingList', 'Caption' => 'Daňové výdaje', 'Default' => '0'),
        'ClaimsList' => array('Type' => 'TClaimsList', 'Caption' => 'Pohledávky', 'Default' => '0'),
        'LiabilitiesList' => array('Type' => 'TLiabilitiesList', 'Caption' => 'Závazky', 'Default' => '0'),
      ),
      //'AfterInsert' => array($this, 'AfterInsertFinanceYear'),
    ));
    $this->System->FormManager->RegisterFormType('TClaimsList', array(
      'Type' => 'ManyToOne',
      'Table' => 'FinanceInvoice',
      'Id' => 'Id',
      'Ref' => '',
      'Filter' => '(`TX`.`Time` <= (SELECT `FinanceYear`.`DateEnd` FROM `FinanceYear` WHERE `FinanceYear`.`Id`=#Id)) '.
        'AND (`TX`.`Time` >= (SELECT `FinanceYear`.`DateStart` FROM `FinanceYear` WHERE `FinanceYear`.`Id`=#Id)) '.
        'AND (SIGN(`TX`.`Value`) > 0)',
    ));
    $this->System->FormManager->RegisterFormType('TLiabilitiesList', array(
      'Type' => 'ManyToOne',
      'Table' => 'FinanceInvoice',
      'Id' => 'Id',
      'Ref' => '',
      'Filter' => '(`TX`.`Time` <= (SELECT `FinanceYear`.`DateEnd` FROM `FinanceYear` WHERE `FinanceYear`.`Id`=#Id)) '.
        'AND (`TX`.`Time` >= (SELECT `FinanceYear`.`DateStart` FROM `FinanceYear` WHERE `FinanceYear`.`Id`=#Id)) '.
        'AND (SIGN(`TX`.`Value`) < 0)',
    ));
    $this->System->FormManager->RegisterFormType('TSpendingList', array(
      'Type' => 'ManyToOne',
      'Table' => 'FinanceOperation',
      'Id' => 'Id',
      'Ref' => '',
      'Filter' => '(`TX`.`Time` <= (SELECT `FinanceYear`.`DateEnd` FROM `FinanceYear` WHERE `FinanceYear`.`Id`=#Id)) '.
        'AND (`TX`.`Time` >= (SELECT `FinanceYear`.`DateStart` FROM `FinanceYear` WHERE `FinanceYear`.`Id`=#Id)) '.
        'AND (SIGN(`TX`.`Value`) < 0)',
    ));
    $this->System->FormManager->RegisterFormType('TIncomeList', array(
      'Type' => 'ManyToOne',
      'Table' => 'FinanceOperation',
      'Id' => 'Id',
      'Ref' => '',
      'Filter' => '(`TX`.`Time` <= (SELECT `FinanceYear`.`DateEnd` FROM `FinanceYear` WHERE `FinanceYear`.`Id`=#Id)) '.
        'AND (`TX`.`Time` >= (SELECT `FinanceYear`.`DateStart` FROM `FinanceYear` WHERE `FinanceYear`.`Id`=#Id)) '.
        'AND (SIGN(`TX`.`Value`) > 0)',
    ));
    $this->System->FormManager->RegisterFormType('TTaxSpendingList', array(
      'Type' => 'ManyToOne',
      'Table' => 'FinanceOperation',
      'Id' => 'Id',
      'Ref' => '',
      'Filter' => '(`TX`.`Time` <= (SELECT `FinanceYear`.`DateEnd` FROM `FinanceYear` WHERE `FinanceYear`.`Id`=#Id)) '.
        'AND (`TX`.`Time` >= (SELECT `FinanceYear`.`DateStart` FROM `FinanceYear` WHERE `FinanceYear`.`Id`=#Id)) '.
        'AND (SIGN(`TX`.`Value`) < 0) AND (`TX`.`Taxable` = 1)',
    ));
    $this->System->FormManager->RegisterFormType('TTaxIncomeList', array(
      'Type' => 'ManyToOne',
      'Table' => 'FinanceOperation',
      'Id' => 'Id',
      'Ref' => '',
      'Filter' => '(`TX`.`Time` <= (SELECT `FinanceYear`.`DateEnd` FROM `FinanceYear` WHERE `FinanceYear`.`Id`=#Id)) '.
        'AND (`TX`.`Time` >= (SELECT `FinanceYear`.`DateStart` FROM `FinanceYear` WHERE `FinanceYear`.`Id`=#Id)) '.
        'AND (SIGN(`TX`.`Value`) > 0) AND (`TX`.`Taxable` = 1)',
    ));
    $this->System->FormManager->RegisterClass('FinanceInvoiceOperationRel', array(
      'Title' => 'Zaplacení faktůr',
      'Table' => 'FinanceInvoiceOperationRel',
      'Items' => array(
        'Invoice' => array('Type' => 'TFinanceInvoice', 'Caption' => 'Faktura', 'Default' => ''),
        'Operation' => array('Type' => 'TFinanceOperation', 'Caption' => 'Platba', 'Default' => ''),
      ),
    ));

    ModuleIS::Cast($this->System->GetModule('IS'))->RegisterDashboardItem('Finance', array($this, 'ShowDashboardItem'));
  }

  function ShowDashboardItem(): string
  {
    $DbResult = $this->Database->select('FinanceOperation', 'ROUND(SUM(`Value`))', '1');
    $DbRow = $DbResult->fetch_row();
    $Output = 'Stav placení: '.$DbRow['0'].' Kč<br/>';
    return $Output;
  }

  function BeforeInsertFinanceOperation(Form $Form): array
  {
    if (array_key_exists('Time', $Form->Values)) $Year = date("Y", $Form->Values['Time']);
      else $Year = date("Y", $Form->Values['ValidFrom']);
    $FinanceGroup = $this->Finance->GetFinanceGroupById($Form->Values['Group'], 'FinanceOperationGroup');
    $Form->Values['BillCode'] = ModuleDocument::Cast($this->System->GetModule('Document'))->GetNextDocumentLineNumberId($FinanceGroup['DocumentLine'], $Year);
    return $Form->Values;
  }

  function AfterInsertFinanceOperation(Form $Form, string $Id): array
  {
    $FinanceGroup = $this->Finance->GetFinanceGroupById($Form->Values['Group'], 'FinanceOperationGroup');
    $this->Database->query('UPDATE `'.$Form->Definition['Table'].'` SET `Value`= '.
      ($Form->Values['ValueUser'] * $FinanceGroup['ValueSign']).' WHERE `Id`='.$Id);
    return $Form->Values;
  }

  function BeforeModifyFinanceOperation(Form $Form, string $Id): array
  {
    $FinanceGroup = $this->Finance->GetFinanceGroupById($Form->Values['Group'], 'FinanceOperationGroup');
    $this->Database->query('UPDATE `'.$Form->Definition['Table'].'` SET `Value`= '.
      ($Form->Values['ValueUser'] * $FinanceGroup['ValueSign']).' WHERE `Id`='.$Id);
    return $Form->Values;
  }

  function BeforeInsertFinanceInvoice(Form $Form): array
  {
    // Get new DocumentLineCode by selected invoice Group
    if (array_key_exists('Time', $Form->Values)) $Year = date("Y", $Form->Values['Time']);
      else $Year = date("Y", $Form->Values['ValidFrom']);
    $Group = $this->Finance->GetFinanceGroupById($Form->Values['Group'], 'FinanceInvoiceGroup');
    $Form->Values['BillCode'] = ModuleDocument::Cast($this->System->GetModule('Document'))->GetNextDocumentLineNumberId($Group['DocumentLine'], $Year);
    return $Form->Values;
  }

  function AfterInsertFinanceInvoice(Form $Form, string $Id): array
  {
    $FinanceGroup = $this->Finance->GetFinanceGroupById($Form->Values['Group'], 'FinanceInvoiceGroup');
    $DbResult = $this->Database->query(str_replace('#Id', $Id, $Form->Definition['Items']['ValueUser']['SQL']));
    $DbRow = $DbResult->fetch_row();
    $Sum = $DbRow[0];

    $this->Database->query('UPDATE `'.$Form->Definition['Table'].'` SET `Value`= '.
      ($Sum * $FinanceGroup['ValueSign']).' WHERE `Id`='.$Id);
    return $Form->Values;
  }

  function BeforeModifyFinanceInvoice(Form $Form, string $Id): array
  {
    $FinanceGroup = $this->Finance->GetFinanceGroupById($Form->Values['Group'], 'FinanceInvoiceGroup');
    $DbResult = $this->Database->query(str_replace('#Id', $Id, $Form->Definition['Items']['ValueUser']['SQL']));
    $DbRow = $DbResult->fetch_row();
    $Sum = $DbRow[0];
    $this->Database->query('UPDATE `'.$Form->Definition['Table'].'` SET `Value`= '.
      ($Sum * $FinanceGroup['ValueSign']).' WHERE `Id`='.$Id);
    return $Form->Values;
  }

  function AfterInsertFinanceInvoiceItem(Form $Form, string $Id): array
  {
    $ParentForm = new Form($this->System->FormManager);
    $ParentForm->SetClass('FinanceInvoice');
    $ParentForm->LoadValuesFromDatabase($Form->Values['FinanceInvoice']);
    $this->AfterInsertFinanceInvoice($ParentForm, $Form->Values['FinanceInvoice']);
    return $Form->Values;
  }

  function AfterModifyFinanceInvoiceItem(Form $Form, string $Id): array
  {
    $ParentForm = new Form($this->System->FormManager);
    $ParentForm->SetClass('FinanceInvoice');
    $ParentForm->LoadValuesFromDatabase($Form->Values['FinanceInvoice']);
    $this->BeforeModifyFinanceInvoice($ParentForm, $Form->Values['FinanceInvoice']);
    return $Form->Values;
  }

  static function Cast(Module $Module): ModuleFinance
  {
    if ($Module instanceof ModuleFinance)
    {
      return $Module;
    }
    throw new Exception('Expected ModuleFinance type but '.gettype($Module));
  }
}