source: trunk/Modules/Finance/UserState.php

<?php

class PageFinanceUserState extends Page
{
  function __construct(System $System)
  {
    parent::__construct($System);
    $this->Title = 'Stav financí';
    $this->Description = 'Stav financí účastníka';
    $this->ParentClass = 'PageUser';
  }

  function ShowFinanceOperation(int $SubjectId): string
  {
    $UserOperationTableQuery = '((SELECT `Text`, `FinanceOperation`.`Time`, `Value`, `File`.`Hash` AS `FileHash`, `BillCode`, NULL AS `PeriodFrom`, NULL AS `PeriodTo`, `DocumentLineCode`.`Name` AS `BillName` '.
      'FROM `FinanceOperation` '.
      'LEFT JOIN `DocumentLineCode` ON `DocumentLineCode`.`Id`=`FinanceOperation`.`BillCode` '.
      'LEFT JOIN `File` ON `File`.`Id`=`FinanceOperation`.`File` '.
      'WHERE (`Subject`='.$SubjectId.') '.
      ') UNION ALL '.
      '(SELECT (SELECT GROUP_CONCAT(`Description` SEPARATOR ",") FROM `FinanceInvoiceItem` WHERE `FinanceInvoice`=`FinanceInvoice`.`Id`) AS `Text`, '.
      '`FinanceInvoice`.`Time`, -`Value`, `File`.`Hash` AS `FileHash`, `BillCode`, `PeriodFrom`, `PeriodTo`, `DocumentLineCode`.`Name` AS `BillName` '.
      'FROM `FinanceInvoice` '.
      'LEFT JOIN `DocumentLineCode` ON `DocumentLineCode`.`Id`=`FinanceInvoice`.`BillCode` '.
      'LEFT JOIN `File` ON `File`.`Id`=`FinanceInvoice`.`File` '.
      'WHERE (`Subject`='.$SubjectId.') AND (`VisibleToUser` = 1)))';

    $Output = '<div style="text-align:center">Výpis finančních operací</div>';
    $DbResult = $this->Database->query('SELECT COUNT(*) FROM '.$UserOperationTableQuery.' AS `T1`');
    $DbRow = $DbResult->fetch_row();
    $PageList = GetPageList('FinanceOperation', $DbRow[0]);

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

    $TableColumns = array(
        array('Name' => 'Time', 'Title' => 'Datum'),
        array('Name' => 'Text', 'Title' => 'Popis'),
        array('Name' => 'Value', 'Title' => 'Změna [Kč]'),
        array('Name' => 'State', 'Title' => 'Zůstatek [Kč]'),
        array('Name' => 'PeriodFrom', 'Title' => 'Období'),
        array('Name' => 'BillCode', 'Title' => 'Doklad'),
    );
    $Order = GetOrderTableHeader('FinanceOperation', $TableColumns, 'Time', 1);
    $Output .= $Order['Output'];

    $StateQuery = 'SELECT SUM(`T2`.`Value`) FROM '.$UserOperationTableQuery.
      ' AS `T2` WHERE `T2`.`Time` <= `T1`.`Time` ';
    $Query = 'SELECT *, ('.$StateQuery.') AS `State` FROM '.$UserOperationTableQuery.' AS `T1` '.$Order['SQL'].$PageList['SQLLimit'];

    $DbResult = $this->Database->query($Query);
    $SumValue = 0;
    while ($Row = $DbResult->fetch_assoc())
    {
      $Row['State'] = round($Row['State'], 2);
      if ($Row['State'] > 0) $Row['State'] = '<span style="color:green;">'.$Row['State'].'</span>';
      if ($Row['State'] < 0) $Row['State'] = '<span style="color:red;">'.$Row['State'].'</span>';
      if ($Row['Value'] == -0) $Row['Value'] = 0;
      if ($Row['Value'] > 0) $Row['Value'] = '+'.$Row['Value'];
      if ($Row['BillName'] == '') $Row['BillName'] = 'PDF';
      if ($Row['FileHash'] != '') $Invoice = '<a href="'.$this->System->Link('/file?h='.$Row['FileHash']).'">'.$Row['BillName'].'</a>';
        else $Invoice = NotBlank($Row['BillName']);
      if ($Row['PeriodFrom'] != '') $Period = HumanDate($Row['PeriodFrom']).' - '.HumanDate($Row['PeriodTo']);
        else $Period = '&nbsp;';
      $Output .= '<tr><td style="text-align: right;">'.HumanDate($Row['Time']).'</td>'.
          '<td style="text-align: left;">'.$Row['Text'].'</td>'.
          '<td style="text-align: right;">'.round($Row['Value'], 2).'</td>'.
          '<td style="text-align: right;">'.$Row['State'].'</td>'.
          '<td>'.$Period.'</td>'.
          '<td>'.$Invoice.'</td></tr>';
      $SumValue = $SumValue + $Row['Value'];
    }
    $Output .= '</table>';
    $Output .= $PageList['Output'];
    return $Output;
  }

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

    // Determine which customer should be displayed
    if (array_key_exists('i', $_GET))
    {
      if (!ModuleUser::Cast($this->System->GetModule('User'))->User->CheckPermission('Finance', 'Manage')) return 'Nemáte oprávnění';
      $CustomerId = $_GET['i'];
    } else
    {
      if (!ModuleUser::Cast($this->System->GetModule('User'))->User->CheckPermission('Finance', 'DisplaySubjectState')) return 'Nemáte oprávnění';
      $UserId = ModuleUser::Cast($this->System->GetModule('User'))->User->User['Id'];
      $DbResult = $this->Database->query('SELECT `Customer` FROM `UserCustomerRel` WHERE `User`='.$UserId.' LIMIT 1');
      if ($DbResult->num_rows > 0)
      {
        $CustomerUserRel = $DbResult->fetch_assoc();
        $CustomerId = $CustomerUserRel['Customer'];
      } else return $this->SystemMessage('Chyba', 'Nejste zákazníkem');
    }

    // Load customer info
    $DbResult = $this->Database->query('SELECT * FROM `Member` WHERE `Id`='.$CustomerId);
    if ($DbResult->num_rows == 1)
    {
      $Customer = $DbResult->fetch_assoc();
    } else return $this->SystemMessage('Položka nenalezena', 'Zákazník nenalezen');

    // Load subject info
    $DbResult = $this->Database->query('SELECT * FROM `Subject` WHERE `Id`='.$Customer['Subject']);
    if ($DbResult->num_rows == 1)
    {
      $Subject = $DbResult->fetch_assoc();
    } else return $this->SystemMessage('Položka nenalezena', 'Subjekt nenalezen');

    $Output = '<table width="100%" border="0" cellspacing="0" cellpadding="3"><tr><td valign="top">';

    // Account state
    $UserOperationTableQuery = '((SELECT `Text`, `Time`, `Value`, `File`, `BillCode`, NULL AS `PeriodFrom`, NULL AS `PeriodTo` '.
      'FROM `FinanceOperation` WHERE (`Subject`='.$Subject['Id'].')) UNION ALL '.
      '(SELECT (SELECT GROUP_CONCAT(`Description` SEPARATOR ",") FROM `FinanceInvoiceItem` WHERE `FinanceInvoice`=`FinanceInvoice`.`Id`) AS `Text`, '.
      '`Time`, -`Value`, `File`, `BillCode`, `PeriodFrom`, `PeriodTo` FROM `FinanceInvoice` WHERE (`Subject`='.$Subject['Id'].')) ORDER BY `Time` DESC) AS `T1`';
    $DbResult = $this->Database->query('SELECT SUM(`T1`.`Value`) AS `Total` FROM '.$UserOperationTableQuery);
    $DbRow = $DbResult->fetch_array();
    $Total = $DbRow['Total'];
    $Output .= 'Stav účtu: <strong>'.round($Total, 2).' Kč</strong><br /><br />';

    // Tabulka operaci
    $Output .= $this->ShowFinanceOperation($Subject['Id']);

    $Output .= '</td><td valign="top">';

    $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`='.$this->System->Config['Finance']['MainSubjectId'].') AND (FinanceBankAccount.`Use`=1)');
    $SubjectFromAccount = $DbResult->fetch_assoc();
    $Account = $SubjectFromAccount['NumberFull'];

    $Output .= 'Účet pro platby: <strong>'.$Account.'</strong><br/>';
    $Output .= 'Variabilní symbol pro platby: <strong>'.$Subject['Id'].'</strong><br/>';
    $Output .= 'Subjekt: <strong>'.$Subject['Name'].'</strong><br/>';
    $Output .= '<br/>';

    $Total = 0;
    $Output .= 'Rozpis měsíčního poplatku:<br><table class="WideTable">'.
      '<tr><th>Služba</th><th>Cena [Kč]</th></tr>';
    $DbResult = $this->Database->query('SELECT `Service`.`Name`, `Service`.`Price` FROM `ServiceCustomerRel` '.
      'LEFT JOIN `Service` ON `Service`.`Id`=`ServiceCustomerRel`.`Service` '.
      'WHERE (`ServiceCustomerRel`.`Customer`='.$Customer['Id'].') AND (`ServiceCustomerRel`.`ChangeAction` IS NULL)');
    while ($DbRow = $DbResult->fetch_assoc())
    {
      $Output .= '<tr><td>'.$DbRow['Name'].'</td><td>'.$DbRow['Price'].'</td></tr>';
      $Total += $DbRow['Price'];
    }

    $Output .= '<tr><td><strong>Celkem</strong></td><td><strong>'.$Total.'</strong></td></tr></table>';
    //echo('Tarif pro příští měsíc: '.$Tarify[$Row2['inet_tarif_next']]['name'].'<br><br>');
    $Output .= '<br />';

    $Output .= '</td></tr></table>';
    return $Output;
  }
}