<?php

class PageFinanceManage extends Page
{
  function __construct(System $System)
  {
    parent::__construct($System);
    $this->Title = 'Správa financí';
    $this->ParentClass = 'PageFinance';
  }

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

    if (array_key_exists('Operation', $_GET)) $Operation = $_GET['Operation'];
      else $Operation = '';
    switch ($Operation)
    {
      case 'Recalculate':
        $Output .= ModuleFinance::Cast($this->System->GetModule('Finance'))->Finance->RecalculateMemberPayment();
        break;
      case 'ShowMonthlyPayment':
        $Output = $this->ShowMonthlyPayment();
        break;
      case 'ProcessMonthlyPayment':
        $Output = $this->ProcessMonthlyPayment();
        break;
      case 'GenerateBills':
        $Output = $this->GenerateBills();
        break;
      case 'RegenerateInvoice':
        $Output = $this->GenerateInvoice('AND (Id='.$_GET['i'].')');
        break;
      case 'RegenerateOperation':
        $Output = $this->GenerateOperation('AND (Id='.$_GET['i'].')');
        break;
      case 'SendPaymentEmail':
        $Output = $this->SendPaymentEmail($_GET['i']);
        break;
      default:
        //$Output .= '<a href="?Operation=Recalculate">Přepočet financí</a><br />';
        $Output .= '<a href="?Operation=ShowMonthlyPayment">Měsíční vyúčtování</a><br />';
        $Output .= '<a href="'.$this->System->Link('/finance/zivnost/').'">Živnost</a><br />';
        $Output .= '<a href="?Operation=GenerateBills">Generovat chybějící doklady</a><br />';
        $Output .= '<a href="'.$this->System->Link('/finance/import/').'">Import plateb</a><br />';
    }
    return $Output;
  }

  /* Get first day and last day of given billing period. Periods are aligned with year start/end. */
  function GetBillingPeriod(int $Period): array
  {
    $Time = time();
    $Year = date('Y', $Time);

    $MonthCount = ModuleFinance::Cast($this->System->GetModule('Finance'))->Finance->BillingPeriods[$Period]['MonthCount'];
    if ($MonthCount <= 0) return array('From' => NULL, 'To' => NULL, 'MonthCount' => 0);
    $MonthCurrent = date('n', $Time);

    /* Get start and end of aligned period */
    $MonthFrom = floor(($MonthCurrent - 1) / $MonthCount) * $MonthCount + 1;
    $MonthTo = $MonthFrom + $MonthCount - 1;

    /* Use period from current month to end month so months before current month are cut out */
    $MonthCount = $MonthTo - $MonthCurrent + 1;
    $MonthFrom = $MonthCurrent;

    /* Get first and last day of period */
    $PeriodFrom = mktime(0, 0, 0, $MonthFrom, 1, $Year);
    $PeriodTo = mktime(0, 0, 0, $MonthTo, date('t', mktime(0, 0, 0, $MonthTo, 1, $Year)), $Year);

    return array('From' => $PeriodFrom, 'To' => $PeriodTo, 'MonthCount' => $MonthCount);
  }

  function ShowMonthlyPayment(): string
  {
    if (!ModuleUser::Cast($this->System->GetModule('User'))->User->CheckPermission('Finance', 'Manage')) return 'Nemáte oprávnění';
    $SQL = 'SELECT `Member`.*, `MemberPayment`.`MonthlyTotal` AS `Monthly`, '.
      '`MemberPayment`.`Cash` AS `Cash`, '.
      '(SELECT GROUP_CONCAT(`Service`.`Name`) FROM `ServiceCustomerRel` LEFT JOIN `Service` '.
      'ON `Service`.`Id`=`ServiceCustomerRel`.`Service` WHERE `ServiceCustomerRel`.`Customer`=`Member`.`Id` AND '.
      '`ServiceCustomerRel`.`ChangeAction` IS NULL) AS `ServicesNextMonth`, '.
      'UNIX_TIMESTAMP(`Member`.`BillingPeriodLastDate`) AS `LastDate`, `Subject`.`Name` AS `SubjectName`, '.
      '`FinanceBillingPeriod`.`Name` AS `BillingPeriodName` '.
      'FROM `MemberPayment` JOIN `Member` ON `Member`.`Id`=`MemberPayment`.`Member` JOIN `Subject` '.
      'ON `Subject`.`Id`=`Member`.`Subject` LEFT JOIN `FinanceBillingPeriod` ON '.
      '`FinanceBillingPeriod`.`Id`=`Member`.`BillingPeriod` WHERE (`Member`.`Blocked` = 0)'.
      'AND (`Member`.`BillingPeriod` > 1) AND (`MemberPayment`.`MonthlyTotal` != 0)';

    $DbResult = $this->Database->query('SELECT COUNT(*) FROM ('.$SQL.') AS T');
    $DbRow = $DbResult->fetch_row();
    $PageList = GetPageList('MonthlyPayment', $DbRow[0]);

    $Output = $PageList['Output'];
    $Output .= '<table class="WideTable" style="font-size: small;">';

    $TableColumns = array(
      array('Name' => 'SubjectName', 'Title' => 'Jméno'),
      array('Name' => 'Monthly', 'Title' => 'Platba'),
      array('Name' => 'Cash', 'Title' => 'Kredit'),
      array('Name' => 'LastDate', 'Title' => 'Poslední fakturace'),
      array('Name' => 'ServicesNextMonth', 'Title' => 'Služby'),
      array('Name' => 'BillingPeriodName', 'Title' => 'Perioda'),
    );
    $Order = GetOrderTableHeader('MonthlyPayment', $TableColumns, 'SubjectName', 0);
    $Output .= $Order['Output'];

    $Query = $SQL.' '.$Order['SQL'].$PageList['SQLLimit'];

    $DbResult = $this->Database->query($Query);
    while ($Row = $DbResult->fetch_assoc())
    {
      $Output .= '<tr>'.
      '<td>'.$Row['SubjectName'].'</td>'.
      '<td>'.$Row['Monthly'].'</td>'.
      '<td>'.$Row['Cash'].'</td>'.
      '<td>'.date('j.n.Y', $Row['LastDate']).'</td>'.
      '<td>'.$Row['ServicesNextMonth'].'</td>'.
      '<td>'.$Row['BillingPeriodName'].'</td>'.
      '</tr>';
    }
    $Output .= '</table>';
    $Output .= $PageList['Output'];
    $Output .= '<a href="?Operation=ProcessMonthlyPayment">Generovat faktury</a>';
    return $Output;
  }

  function InsertInvoice(string $Subject, string $TimeCreation, string $TimeDue, array $Items,
    array $Group, float $PeriodFrom, float $PeriodTo): string
  {
    global $LastInsertTime;

    $Finance = ModuleFinance::Cast($this->System->GetModule('Finance'))->Finance;

    $Year = date('Y', $TimeCreation);
    $BillCode = ModuleDocument::Cast($this->System->GetModule('Document'))->GetNextDocumentLineNumberId($Group['DocumentLine'], $Year);
    $SumValue = 0;
    foreach ($Items as $Item) {
      $SumValue = $SumValue + $Item['Price'] * $Item['Quantity'];
    }
    $SumValue = round($SumValue, $Finance->Rounding);
    $this->Database->insert('FinanceInvoice', array(
      'Subject' => $Subject, 'Time' => TimeToMysqlDateTime($TimeCreation),
      'TimeDue' => TimeToMysqlDateTime($TimeDue), 'Value' => $SumValue * $Group['ValueSign'],
      'BillCode' => $BillCode,
      'PeriodFrom' => TimeToMysqlDate($PeriodFrom), 'PeriodTo' => TimeToMysqlDate($PeriodTo),
      'Generate' => 1, 'Group' => $Group['Id']));
    $InvoiceId = $this->Database->insert_id;
    foreach ($Items as $Item)
      $this->Database->insert('FinanceInvoiceItem', array('FinanceInvoice' => $InvoiceId,
        'Description' => $Item['Description'], 'Price' => $Item['Price'],
        'Quantity' => $Item['Quantity'], 'VAT' => $Item['VAT']));
    //$LastInsertTime = $Time;
    //$this->CheckAdvancesAndLiabilities($Subject);
    return $InvoiceId;
  }

  function ProduceInvoices(): string
  {
    $Output = '';

    // Produce accounting items
    $DbResult = $this->Database->query('SELECT `Member`.*, `MemberPayment`.`MonthlyTotal` AS `MonthlyTotal`, '.
      'UNIX_TIMESTAMP(`Member`.`BillingPeriodLastDate`) AS `BillingPeriodLastUnixTime`, `Subject`.`Name` AS `SubjectName`,'.
      '`MemberPayment`.`MonthlyPlus` AS `MonthlyPlus` '.
      'FROM `MemberPayment` JOIN `Member` ON `Member`.`Id`=`MemberPayment`.`Member` '.
      'JOIN `Subject` ON `Subject`.`Id`=`Member`.`Subject`');
    while ($Member = $DbResult->fetch_assoc())
    {
      $Output .= $Member['SubjectName'].': ';
      $Period = $this->GetBillingPeriod($Member['BillingPeriod']);

      // Check if need to produce new invoice for customer
      if (($Period['MonthCount'] > 0) and ($Member['Blocked'] == 0) and
        ($Period['From'] > $Member['BillingPeriodLastUnixTime']))
      {
        $InvoiceItems = array();
        $MonthlyTotal = 0;
        $DbResult2 = $this->Database->query('SELECT `Service`.* '.
          'FROM `ServiceCustomerRel` LEFT JOIN `Service` '.
          'ON `Service`.`Id`=`ServiceCustomerRel`.`Service` '.
          'WHERE (`ServiceCustomerRel`.`Customer`='.
          $Member['Id'].') AND (`ServiceCustomerRel`.`ChangeAction` IS NULL) ');
        while ($Service = $DbResult2->fetch_assoc())
        {
          $InvoiceItems[] = array('Description' => $Service['Name'], 'Price' => $Service['Price'],
            'Quantity' => $Period['MonthCount'], 'VAT' => ModuleFinance::Cast($this->System->GetModule('Finance'))->Finance->GetVATByType($Service['VAT']));
          $MonthlyTotal += $Service['Price'];
        }
        $PayPerPeriod = $MonthlyTotal * $Period['MonthCount'];
        // We can't produce negative invoice except storno invoice.
        // TODO: In case of negative invoice it is not sufficient to reverse invoicing direction
        // Other subject should invoice only positive items. Negative items should be somehow removed.
        if ($MonthlyTotal >= 0)
        {
          $InvoiceGroupId = INVOICE_GROUP_OUT;
        } else
        {
          $InvoiceGroupId = INVOICE_GROUP_IN;
        }

        // Load invoice group
        $FinanceGroup = ModuleFinance::Cast($this->System->GetModule('Finance'))->Finance->GetFinanceGroupById($InvoiceGroupId, 'FinanceInvoiceGroup');
        foreach ($InvoiceItems as $Index => $Item)
        {
          $InvoiceItems[$Index]['Price'] = $Item['Price'] * $FinanceGroup['ValueSign'];
        }

        if ($PayPerPeriod != 0)
        {
          $TimePeriodText = date('j.n.Y', $Period['From']).' - '.date('j.n.Y', $Period['To']);
          $Output .= $TimePeriodText.': '.$MonthlyTotal.' * '.$Period['MonthCount'].' = '.$PayPerPeriod;
          $this->InsertInvoice($Member['Subject'], time(), time() + 3600 * 24 * INVOICE_DUE_DAYS,
            $InvoiceItems, $FinanceGroup, $Period['From'], $Period['To']);

          $Output .= $this->SendPaymentEmail($Member['Id']);
        }
        // Update last billing day
        $this->Database->update('Member', '`Id`='.$Member['Id'],
          array('BillingPeriodLastDate' => TimeToMysqlDateTime($Period['To'])));
      }
      $Output .= "\n";
    }
    return $Output;
  }

  function TableUpdateChanges(string $Table): void
  {
    $Time = time();
    $DbResult = $this->Database->select($Table, '*', '(`ChangeAction` IS NOT NULL) AND '.
      '(`ChangeTime` <= "'.TimeToMysqlDateTime($Time).'") ORDER BY `ChangeTime` ASC');
    while ($Service = $DbResult->fetch_assoc())
    {
      if ($Service['ChangeAction'] == 'add')
      {
        unset($Service['Id']);
        unset($Service['ChangeReplaceId']);
        unset($Service['ChangeAction']);
        unset($Service['ChangeTime']);
        $this->Database->insert($Table, $Service);
      } else
      if ($Service['ChangeAction'] == 'modify')
      {
        unset($Service['Id']);
        unset($Service['ChangeAction']);
        $ReplaceId = $Service['ChangeReplaceId'];
        unset($Service['ChangeReplaceId']);
        unset($Service['ChangeTime']);
        $this->Database->update($Table, '`Id`='.$ReplaceId, $Service);
      } else
      if ($Service['ChangeAction'] == 'delete')
      {
        $this->Database->delete($Table, '`Id`='.$Service['ReplaceId']);
      }
    }
    $this->Database->delete($Table, '(`ChangeAction` IS NOT NULL) AND (`ChangeTime` <= "'.TimeToMysqlDateTime($Time).'")');
  }

  function ProcessTableUpdates(): string
  {
    // Update customers
    $Output = 'Měním zákazníky...'."\n";
    $this->TableUpdateChanges('Member');

    // Update finance charge
    $Output = 'Měním aktuální parametry sítě...'."\n";
    $this->TableUpdateChanges('FinanceCharge');

    // Update services
    $Output .= 'Aktualizuji služby....'."\n";
    $this->TableUpdateChanges('Service');

    // Update customer service selections
    $Output .= 'Aktualizuji výběr služeb zákazníků....'."\n";
    $this->TableUpdateChanges('ServiceCustomerRel');

    return $Output;
  }

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

    $Output .= $this->ProcessTableUpdates();

    $Finance = &ModuleFinance::Cast($this->System->GetModule('Finance'))->Finance;
    $Finance->LoadMonthParameters(0);

    // Načti poslední měsíční přehled a nastavení
    $DbResult = $this->Database->select('FinanceMonthlyOverall', '*', '1 ORDER BY `Date` DESC LIMIT 1');
    $Overall = $DbResult->fetch_array();

    $Output .= 'Datum: '.date('j.n.Y')."\n";

    $DateParts = explode('-', $Overall['Date']);
    $MonthLast = $DateParts[1];
    $YearLast = $DateParts[0];
    $MonthCurrent = date('m') + 0;
    $YearCurrent = date('Y') + 0;

    $Output .= $Finance->RecalculateMemberPayment();

    $DbResult = $this->Database->query('SELECT SUM(`Cash`) FROM `MemberPayment`');
    $Row = $DbResult->fetch_row();
    $TotalMemberCash = $Row[0];
    $Output .= 'Stav pokladny: Členové('.round($TotalMemberCash).')'."\n";

    $DbResult = $this->Database->query('SELECT SUM(`Product`.`Consumption`) AS `Consumption` FROM `StockSerialNumber` '.
      'JOIN `Product` ON `StockSerialNumber`.`Product` = `Product`.`Id` WHERE (`StockSerialNumber`.`TimeElimination` IS NULL) ');
    $Row = $DbResult->fetch_row();
    $TotalConsumption = $Row[0];
    $TotalConsumptionCost = $Finance->W2Kc($TotalConsumption);

    $SpravaCelkem = $Finance->Sprava * $Finance->SpravaUsers;
    $Output .= 'Kontrola placení (Zaplaceno-Sprava-Internet): '.$Finance->TotalPaid.'-'.$SpravaCelkem.'-'.$Finance->Internet.'='.($Finance->TotalPaid - $SpravaCelkem - $Finance->Internet)."\n";

    // Zkontrolovat odečtení měsíčního poplatku
    $Output .= 'Kontrola odečtení poplatků: Poslední měsíc-'.$MonthLast.' Aktuální měsíc-'.$MonthCurrent."\n";
    if (($MonthCurrent != $MonthLast) or ($YearCurrent != $YearLast))
    {
      $Output .= 'Odečítám pravidelný poplatek...'."\n";
      $Output .= $this->ProduceInvoices();

      $Output .= 'Přidávám měsíční přehled...'."\n";
      $DbResult = $this->Database->query('SELECT * FROM `FinanceCharge` WHERE (`ChangeAction` IS NULL) LIMIT 1');
      $Charge = $DbResult->fetch_assoc();
      $this->Database->insert('FinanceMonthlyOverall', array('Date' => 'NOW()',
        'Money' => $Finance->Internet, 'kWh' => $Finance->kWh,
        'Administration' => $Finance->Sprava, 'AdministrationTotal' => $SpravaCelkem,
        'ConsumptionTotal' => $TotalConsumptionCost, 'TotalPaid' => $Finance->TotalPaid,
        'BaseTariffPrice' => $Charge['BaseTariffPrice'],
        'TopTariffPrice' => $Charge['TopTariffPrice'], 'MemberCount' => $Finance->InternetUsers));

      $Finance->RecalculateMemberPayment();

      // Restart traffic shaping
      //$this->Database->update('NetworkConfiguration', 'Id = 3', array('Changed' => 1));
      //flush();
      //$this->GenerateBills();
      ModuleLog::Cast($this->System->GetModule('Log'))->NewRecord('Finance', 'ProcessMonthlyPayment', $Output);
    }
    $Output = str_replace("\n", '<br/>', $Output);
    return $Output;
  }

  function SendPaymentEmail(string $MemberId, string $FileId = ''): string
  {
    $Finance = &ModuleFinance::Cast($this->System->GetModule('Finance'))->Finance;
    $Finance->LoadMonthParameters(0);

    if (!ModuleUser::Cast($this->System->GetModule('User'))->User->CheckPermission('Finance', 'Manage')) return 'Nemáte oprávnění';
    global $Config;

    $DbResult = $this->Database->select('Member', '*', '`Id`='.$MemberId);
    $Member = $DbResult->fetch_assoc();

    $DbResult = $this->Database->select('MemberPayment', '*', '`Member`='.$MemberId);
    $MemberPayment = $DbResult->fetch_assoc();

    $DbResult = $this->Database->select('Subject', 'Name', '`Id`='.$Member['Subject']);
    $Subject = $DbResult->fetch_assoc();

    $DbResult = $this->Database->select('User', '*', '`Id`='.$Member['ResponsibleUser']);
    $User = $DbResult->fetch_assoc();

    $DbResult = $this->Database->select('Subject', '*', '`Id`='.$Config['Finance']['MainSubjectId']);
    $MainSubject = $DbResult->fetch_assoc();

    $DbResult = $this->Database->select('Company', '*', '`Id`='.$Config['Finance']['MainCompanyId']);
    $MainCompany = $DbResult->fetch_assoc();

    $Period = $this->GetBillingPeriod($Member['BillingPeriod']);

    $DbResult = $this->Database->query('SELECT `FinanceBankAccount`.*, '.
      'CONCAT(`FinanceBankAccount`.`Number`, "/", `FinanceBank`.`Code`) AS `NumberFull` FROM `FinanceBankAccount` '.
      'JOIN `FinanceBank` ON `FinanceBank`.`Id`=`FinanceBankAccount`.`Bank` '.
      'WHERE (`FinanceBankAccount`.`Subject`='.$Config['Finance']['MainSubjectId'].') '.
      'AND (`FinanceBankAccount`.`Use`=1)');
    $MainSubjectAccount = $DbResult->fetch_assoc();

    if ($User['Email'] != '')
    {
      $Title = 'Pravidelné vyúčtování služeb '.$MainCompany['Name'];
      $Content = 'Dobrý den,<br/><br/>'.
        'Zasíláme vyúčtování klienta <strong>'.$Subject['Name'].'</strong> zastoupeného uživatelem <strong>'.
        $User['Name'].'</strong> ke dni <strong>'.Core::Cast($this->System)->HumanDate(time()).'</strong>.<br/><br/>'."\n".
        'Vaše aktuální služby: ';
      $DbResult = $this->Database->query('SELECT GROUP_CONCAT(`Service`.`Name`) AS `Name` FROM `ServiceCustomerRel` LEFT JOIN `Service` '.
        'ON `Service`.`Id`=`ServiceCustomerRel`.`Service` WHERE (`ServiceCustomerRel`.`Customer`='.$Member['Id'].') '.
        'AND (`ServiceCustomerRel`.`ChangeAction` IS NULL)');
      $Service = $DbResult->fetch_assoc();
      $Content .= '<strong>'.$Service['Name'].'</strong><br />'."\n".
        'Vaše platební období: <strong>'.ModuleFinance::Cast($this->System->GetModule('Finance'))->Finance->BillingPeriods[$Member['BillingPeriod']]['Name'].'</strong><br />'."\n".
        'Pravidelná platba za období: <strong>'.($MemberPayment['MonthlyTotal'] * $Period['MonthCount']).' Kč</strong><br />'."\n".
        'Bankovní účet: <strong>'.$MainSubjectAccount['NumberFull'].'</strong><br/>'."\n".
        'Variabilní symbol: <strong>'.$Member['Subject'].'</strong><br/>'."\n".
        'Stav vašeho účtu: <strong>'.($MemberPayment['Cash'] - $MemberPayment['MonthlyTotal'] * $Period['MonthCount']).' Kč</strong><br /><br />'."\n";
      $Content .= 'Nové finanční operace:<br/>'.
        '<table style="margin-left: auto; margin-right: auto; border-style: solid; border-width: 1px; border-collapse: collapse;">'.
        '<tr><th style="border-style: solid; border-width: 1px; padding: 1px 5px 1px 5px; text-align: center; font-weight: bold;">Čas</th>'.
        '<th style="border-style: solid; border-width: 1px; padding: 1px 5px 1px 5px; text-align: center; font-weight: bold;">Popis</th>'.
        '<th style="border-style: solid; border-width: 1px; padding: 1px 5px 1px 5px; text-align: center; font-weight: bold;">Částka [Kč]</th></tr>'."\n";
      if ($Member['PaymentEmailTime'] <> 'NULL')
      {
        $Where = ' WHERE (`T1`.`Time` > "'.$Member['PaymentEmailTime'].'")';
      } else 
      {
        $Where = '';
      }
      $DbResult = $this->Database->query('SELECT T1.* FROM ((SELECT `Text`, `Time`, `Value`, `File` FROM `FinanceOperation` WHERE (`Subject`='.$Member['Subject'].')) UNION ALL '.
        '(SELECT (SELECT GROUP_CONCAT(`Description` SEPARATOR ", ") FROM `FinanceInvoiceItem` '.
        'WHERE `FinanceInvoiceItem`.`FinanceInvoice` = `FinanceInvoice`.`Id`) AS `Text`, '.
        '`Time`, -`Value`, `File` FROM `FinanceInvoice` WHERE (`Subject`='.$Member['Subject'].')) ORDER BY `Time` DESC) AS `T1`'.$Where);
      while ($DbRow = $DbResult->fetch_assoc())
      {
        $Text = $DbRow['Text'];
        $Content .= '<tr><td style="border-style: solid; border-width: 1px; padding: 1px 5px 1px 5px; text-align: center;">'.HumanDate($DbRow['Time']).'</td>'.
          '<td style="border-style: solid; border-width: 1px; padding: 1px 5px 1px 5px; text-align: center;">'.$Text.'</td>'.
          '<td style="border-style: solid; border-width: 1px; padding: 1px 5px 1px 5px; text-align: center;">'.$DbRow['Value'].'</td></tr>'."\n";
      }
      $Content .= '</table><br />'."\n".
      'Pro aktuální informace, prohlížení elektronických dokladů a možnost změny údajů se prosím přihlaste na stránkách '.
      '<a href="https://'.$Config['Web']['Host'].$Config['Web']['RootFolder'].'">https://'.
      $Config['Web']['Host'].$Config['Web']['RootFolder'].'</a>.<br/><br/>'."\n";

      $Content .= '<br/>Tento email je generován automaticky. V případě zjištění nesrovnalostí prosím napište zpět.';
      
      $Content .= '<br/><br/>S pozdravem,<br/>'.$MainCompany['Name'].'<br/><a href="'.$MainSubject['WWW'].'">'.
        str_replace(array('https:', '/'), '', $MainSubject['WWW']).'</a>';

      ModuleEmailQueue::Cast($this->System->GetModule('EmailQueue'))->AddItem($User['Name'].' <'.$User['Email'].'>', $Title, $Content,
         $Config['Web']['Admin'].' <'.$Config['Web']['AdminEmail'].'>');
      $this->Database->update('Member', 'Id='.$Member['Id'], array('PaymentEmailTime' => 'NOW()'));
      $Output = '';      
    } else $Output = 'Uživatel '.$User['Name'].' nemá email.';
    return $Output;
  }

  function GenerateInvoice(string $Where): string
  {
    $DirectoryId = ModuleFinance::Cast($this->System->GetModule('Finance'))->Finance->DirectoryId;
    $Output = '';
    $DbResult = $this->Database->query('SELECT * FROM `FinanceInvoice` WHERE (`BillCode` <> "") '.
      'AND (`Value` != 0) AND (`Generate` = 1)'.$Where);
    while ($Row = $DbResult->fetch_assoc())
    {
      if ($Row['File'] == null)
      {
        $this->Database->insert('File', array('Name' => '', 'Size' => 0, 'Directory' => $DirectoryId, 'Time' => 'NOW()',
          'Hash' => 'SHA1(CONCAT(Id,Name,Size,Time))'));
        $FileId = $this->Database->insert_id;
      } else $FileId = $Row['File'];
      $FileName = 'doklad-'.$FileId.'.pdf';
      $Bill = new BillInvoice($this->System);
      $Bill->InvoiceId = $Row['Id'];
      $FullFileName = ModuleFile::Cast($this->System->GetModule('File'))->File->GetDir($DirectoryId).$FileName;
      $Bill->SaveToFile($FullFileName);
      if (file_exists($FullFileName))
      {
        $this->Database->update('File', 'Id='.$FileId, array('Name' => $FileName, 'Size' => filesize($FullFileName), 'Hash' => 'SHA1(CONCAT(Id,Name,Size,Time))'));
        $this->Database->update('FinanceInvoice', 'Id='.$Row['Id'], array('File' => $FileId));
        $Output .= 'Faktura '.$Row['Id'].' vygenerována do souboru '.$FileName.'<br/>'."\n";
      } else $Output .= 'Soubor "'.$FullFileName.'" se nepodařilo uložit.';
    }
    return $Output;
  }

  function GenerateOperation(string $Where): string
  {
    $DirectoryId = ModuleFinance::Cast($this->System->GetModule('Finance'))->Finance->DirectoryId;
    $Output = '';
    $DbResult = $this->Database->query('SELECT * FROM `FinanceOperation` WHERE (`BillCode` <> "") '.
        'AND (`Value` != 0) AND (`Generate` = 1)'.$Where);
    while ($Row = $DbResult->fetch_assoc())
    {
      if ($Row['File'] == null)
      {
        $DbResult2 = $this->Database->insert('File', array('Name' => '', 'Size' => 0,
          'Directory' => $DirectoryId, 'Time' => 'NOW()', 'Hash' => 'SHA1(CONCAT(Id,Name,Size,Time))'));
        $FileId = $this->Database->insert_id;
      } else $FileId = $Row['File'];
      $FileName = 'doklad2-'.$FileId.'.pdf';
      $Bill = new BillOperation($this->System);
      $Bill->OperationId = $Row['Id'];
      $FullFileName = ModuleFile::Cast($this->System->GetModule('File'))->File->GetDir($DirectoryId).$FileName;
      $Bill->SaveToFile($FullFileName);
      if (file_exists($FullFileName))
      {
        $this->Database->update('File', 'Id='.$FileId, array('Name' => $FileName, 'Size' => filesize($FullFileName), 'Hash' => 'SHA1(CONCAT(Id,Name,Size,Time))'));
        $this->Database->update('FinanceOperation', 'Id='.$Row['Id'], array('File' => $FileId));
        $Output .= 'Doklad pro platbu '.$Row['Id'].' vygenerován do souboru '.$FileName.'<br/>'."\n";
      } else $Output .= 'Soubor "'.$FullFileName.'" se nepodařilo uložit.';
    }
    return $Output;
  }

  function GenerateBills(): string
  {
    $Output = '';
    // Generate PDF files for new invoices and operations
    $Output .= $this->GenerateInvoice(' AND (`File` IS NULL)');
    $Output .= $this->GenerateOperation(' AND (`File` IS NULL)');
    return $Output;
  }
}