<?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');

// TODO: Move constants to Finance module configuration
define('TARIFF_FREE', 7);
define('INVOICE_DUE_DAYS', 15);
define('OPERATION_GROUP_TREASURY_IN', 1);
define('OPERATION_GROUP_TREASURY_OUT', 2);
define('OPERATION_GROUP_ACCOUNT_IN', 3);
define('OPERATION_GROUP_ACCOUNT_OUT', 4);
define('INVOICE_GROUP_IN', 1);
define('INVOICE_GROUP_OUT', 2);
define('VAT_TYPE_BASE', 2);
define('FINANCE_DIRECTION_OUT', 1);
define('FINANCE_DIRECTION_IN', 0);

class Finance extends Model
{
  public string $kWh;
  public string $Internet;
  public string $Sprava;
  public string $DatumOdecteni;
  public string $InternetUsers;
  public string $SpravaUsers;
  public string $MaxSpeed;
  public string $RealMaxSpeed;
  public string $SpeedReserve;
  public string $BaseSpeedElement;
  public string $BaseTariffPrice;
  public string $TopTariffPrice;
  public string $TotalPaid;
  public string $TotalInternetPaid;
  public string $MainSubject;
  public array $BillingPeriods;
  public string $DirectoryId;
  public string $Rounding;

  function LoadMonthParameters(int $Period = 1) // 0 - now, 1 - next month
  {
    $DbResult = $this->Database->query('SELECT * FROM `FinanceBillingPeriod`');
    while ($BillingPeriod = $DbResult->fetch_assoc())
      $this->BillingPeriods[$BillingPeriod['Id']] = $BillingPeriod;

    // Period parameter is not used as it have to be determined from item replacement
    $DbResult = $this->Database->query('SELECT * FROM `FinanceCharge` WHERE (`ChangeAction` IS NULL) LIMIT 1');
    $Row = $DbResult->fetch_array();
    $this->kWh = $Row['kWh'];
    $this->Internet = $Row['Internet'];
    $this->Sprava = $Row['AdministrationPerUser'];
    $this->RealMaxSpeed = $Row['InternetSpeed'];
    $this->SpeedReserve = $Row['InternetSpeedReserve'];
    $this->BaseSpeedElement = $Row['BaseSpeedElement'];
    $this->MaxSpeed = $this->RealMaxSpeed - $this->SpeedReserve;
    $this->TopTariffPrice = $Row['TopTariffPrice'];
    $this->BaseTariffPrice = $Row['BaseTariffPrice'];

    $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Member`');
    $Row = $DbResult->fetch_row();
    $this->InternetUsers = $Row[0];
    $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Member` WHERE (`Blocked`=0) AND (`BillingPeriod` > 1)');
    $Row = $DbResult->fetch_row();
    $this->PayingUsers = $Row[0];

    $this->SpravaUsers = $this->PayingUsers;

    $DbResult = $this->Database->query('SELECT SUM(`MemberPayment`.`MonthlyInternet`) AS `MonthlyInternet`, '.
        'SUM(`MemberPayment`.`MonthlyTotal`) AS `MonthlyTotal` '.
        'FROM `MemberPayment` JOIN `Member` ON `Member`.`Id`=`MemberPayment`.`Member` WHERE `Member`.`Blocked`=0');
    $Row = $DbResult->fetch_assoc();
    $this->TotalInternetPaid = $Row['MonthlyInternet'];
    $this->TotalPaid = $Row['MonthlyTotal'];
    $this->Rounding = $this->System->Config['Finance']['Rounding'];
  }

  function W2Kc($Spotreba): string
  {
    return round($Spotreba * 0.72 * $this->kWh);
  }

  function CreateFinanceYear(int $Year)
  {
    $StartTime = mktime(0, 0, 0, 1, 1, $Year);
    $EndTime = mktime(0, 0, 0, 12, 31, $Year);
    $this->Database->insert('FinanceYear', array('Year' => $Year,
	    'DateStart' => TimeToMysqlDate($StartTime), 'DateEnd' => TimeToMysqlDate($EndTime), 'Closed' => 0));
	  $YearId = $this->Database->insert_id;

    // Create DocumentLineSequence from previous
    $DbResult = $this->Database->select('DocumentLine', 'Id', '`Yearly` = 1');
    while ($DbRow = $DbResult->fetch_assoc())
    {
	    $this->Database->insert('DocumentLineSequence', array('FinanceYear' => $YearId,
	      'NextNumber' => 1, 'YearPrefix' => 1, 'DocumentLine' => $DbRow['Id']));
	  }
  }

  function GetFinanceYear(int $Year): array
  {
    if ($Year == 0)
    {
      // Get latest year
      $DbResult = $this->Database->select('FinanceYear', '*', '1 ORDER BY `Year` DESC LIMIT 1');
    } else $DbResult = $this->Database->select('FinanceYear', '*', '`Year`='.$Year);
    if ($DbResult->num_rows == 0)
    {
	    if ($Year == date('Y'))
	    {
		    $this->CreateFinanceYear($Year);
        $DbResult = $this->Database->select('FinanceYear', '*', '`Year`='.$Year);
	    } else throw new Exception('Rok '.$Year.' nenalezen');
	  }
    $FinanceYear = $DbResult->fetch_assoc();
    if ($FinanceYear['Closed'] == 1)
      throw new Exception('Rok '.$FinanceYear['Year'].' je již uzavřen. Nelze do něj přidávat položky.');
    return $FinanceYear;
  }

  function GetNextDocumentLineNumber(string $Id, int $FinanceYear = 0): string
  {
	  $FinanceYear = $this->GetFinanceYear($FinanceYear);

    $DbResult = $this->Database->query('SELECT `Shortcut`, `Id` FROM `DocumentLine` WHERE `Id`='.$Id);
    $DocumentLine = $DbResult->fetch_assoc();

    $DbResult = $this->Database->query('SELECT * FROM `DocumentLineSequence` WHERE '.
      '`DocumentLine`='.$Id.' AND `FinanceYear`='.$FinanceYear['Id']);
    $Sequence = $DbResult->fetch_assoc();

    if ($Sequence['YearPrefix'] == 1)
    {
      $Result = $DocumentLine['Shortcut'].$Sequence['NextNumber'].'/'.$FinanceYear['Year'];
    } else $Result = $DocumentLine['Shortcut'].$Sequence['NextNumber'];

    $this->Database->query('UPDATE `DocumentLineSequence` SET `NextNumber` = `NextNumber` + 1 '.
      'WHERE (`DocumentLine`='.$Id.') AND (`FinanceYear`='.$FinanceYear['Id'].')');
    return $Result;
  }

  function GetNextDocumentLineNumberId(string $Id, int $FinanceYear = 0): int
  {
    $Code = $this->GetNextDocumentLineNumber($Id, $FinanceYear);
    $this->Database->insert('DocumentLineCode', array('DocumentLine' => $Id, 'Name' => $Code));
    return $this->Database->insert_id;
  }

  function GetFinanceGroupById(string $Id, string $Table): ?array
  {
    $DbResult = $this->Database->query('SELECT * FROM `'.$Table.'` WHERE `Id`= '.$Id);
    if ($DbResult->num_rows == 1) {
      $Group = $DbResult->fetch_assoc();
      return $Group;
    }
    echo('Finance group id '.$Id.' not found in table '.$Table);
    return null;
  }

  function RecalculateMemberPayment(): string
  {
    $Output = 'Aktualizuji finance členů...<br />';
    $this->Database->query('TRUNCATE TABLE `MemberPayment`');
    $DbResult = $this->Database->query('SELECT * FROM `Member`');
    while ($Member = $DbResult->fetch_assoc())
    {
      $DbResult2 = $this->Database->query('SELECT ((SELECT COALESCE(SUM(`Value`), 0) FROM `FinanceOperation` '.
          'WHERE `Subject`='.$Member['Subject'].') - (SELECT COALESCE(SUM(`Value`), 0) FROM `FinanceInvoice` '.
          'WHERE `Subject`='.$Member['Subject'].')) AS `Cash`');
      $Cash = $DbResult2->fetch_row();
      $Cash = $Cash[0];

      $DbResult2 = $this->Database->query('SELECT SUM(`Product`.`Consumption`) * `StockSerialNumber`.`Amount` '.
          'FROM `StockSerialNumber` JOIN `Product` ON `Product`.`Id` = `StockSerialNumber`.`Product` '.
          'WHERE (`StockSerialNumber`.`Location` = '.$Member['Id'].') AND (`StockSerialNumber`.`TimeElimination` IS NULL)');
      $ConsumptionPlus = $DbResult2->fetch_row();
      $ConsumptionPlus = $ConsumptionPlus[0];

      $DbResult2 = $this->Database->query('SELECT SUM(`Service`.`Price`) AS `Price` '.
          'FROM `ServiceCustomerRel` LEFT JOIN '.
          '`Service` ON `Service`.`Id` = `ServiceCustomerRel`.`Service` WHERE (`ServiceCustomerRel`.`Customer`='.
          $Member['Id'].') AND (`ServiceCustomerRel`.`ChangeAction` IS NULL)');
      $DbRow = $DbResult2->fetch_assoc();
      $Monthly = 0;
      if ($DbRow['Price'] != '') $MonthlyInet = $DbRow['Price'];
      else $MonthlyInet = 0;

      $Monthly += $MonthlyInet;
      //$Monthly -= $this->W2Kc($ConsumptionPlus);
      $Monthly = round($Monthly);

      if ($Member['BillingPeriod'] == 1)
      {
        // Inactive payer
        $MonthlyInet = 0;
        $Monthly = 0;
        $ConsumptionPlus = 0;
        $Consumption = 0;
      }
      $Consumption = 0;
      $this->Database->insert('MemberPayment', array('Member' => $Member['Id'],
        'MonthlyInternet' => $MonthlyInet,
        'MonthlyTotal' => $Monthly, 'MonthlyConsumption' => $this->W2Kc($Consumption),
        'Cash' => $Cash, 'MonthlyPlus' => $this->W2Kc($ConsumptionPlus)));
    }
    ModuleLog::Cast($this->System->GetModule('Log'))->NewRecord('Finance', 'RecalculateMemberPayment');
    return $Output;
  }

  function GetVATByType(string $TypeId): string
  {
    $Time = time();
    $DbResult = $this->Database->select('FinanceVAT', 'Value', '(Type='.$TypeId.
      ') AND (ValidFrom <= "'.TimeToMysqlDate($Time).'") AND ((ValidTo >= "'.
      TimeToMysqlDate($Time).'") OR (ValidTo IS NULL)) LIMIT 1');
    $Row = $DbResult->fetch_array();
    return $Row[0];
  }
}

class ModuleFinance extends AppModule
{
  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('File', 'EmailQueue');

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

  function DoInstall(): void
  {
  }

  function DoUninstall(): void
  {
  }

  function DoStart(): void
  {
    global $Config;

    $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' => 'Date', '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' => ''),
        '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'),
      '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->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`), '.$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' => 'Storno doklady', 'Default' => ''),
        'StornoOf' => array('Type' => 'TFinanceInvoiceStornoListOf', 'Caption' => 'Původní 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'),
      '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`, '.$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), '.$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',
      '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)'),
      ),
    ));
    $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->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),
        '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->RegisterClass('Contract', array(
      'Title' => 'Smlouvy',
      'Table' => 'Contract',
      'Items' => array(
        'DocumentLine' => array('Type' => 'TDocumentLine', 'Caption' => 'Dokladová řada', 'Default' => ''),
        'BillCode' => array('Type' => 'TDocumentLineCode', 'Caption' => 'Kód', 'Default' => '', 'Null' => true),
        'Subject' => array('Type' => 'TSubject', 'Caption' => 'Subjekt', 'Default' => ''),
        'ValidFrom' => array('Type' => 'Date', 'Caption' => 'Platnost od', 'Default' => ''),
        'ValidTo' => array('Type' => 'Date', 'Caption' => 'Platnost do', 'Default' => '', 'Null' => true),
        'File' => array('Type' => 'TFile', 'Caption' => 'Soubor', 'Default' => '', 'Null' => true),
      ),
      'BeforeInsert' => array($this, 'BeforeInsertContract'),
    ));
    $this->System->FormManager->RegisterFormType('TContract', array(
      'Type' => 'Reference',
      'Table' => 'Contract',
      'Id' => 'Id',
      'Name' => 'BillCode',
      'Filter' => '1',
    ));
    $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->Finance->MainSubject = $Config['Finance']['MainSubjectId'];
    $this->Finance->DirectoryId = $Config['Finance']['DirectoryId'];

    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 DoStop(): void
  {
  }

  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'] = $this->Finance->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'] = $this->Finance->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;
  }

  function BeforeInsertContract(Form $Form): array
  {
    if (array_key_exists('Time', $Form->Values)) $Year = date("Y", $Form->Values['Time']);
      else $Year = date("Y", $Form->Values['ValidFrom']);
    $Form->Values['BillCode'] = $this->Finance->GetNextDocumentLineNumberId($Form->Values['DocumentLine'], $Year);
    return $Form->Values;
  }

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