source: trunk/Modules/Finance/UserState.php@ 700

<?php

class PageFinanceUserState extends Page
{
  var $FullTitle = 'Stav financí účastníka';
  var $ShortTitle = 'Stav financí';
  var $ParentClass = 'PageFinance';

  function ShowFinanceOperation($Subject)
  {
        $UserOperationTableQuery = '((SELECT Text, Time, Value AS 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 as Value, File, BillCode, PeriodFrom, PeriodTo FROM FinanceInvoice WHERE (Subject='.$Subject['Id'].')))';

    $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($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($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['BillCode'] == '') $Row['BillCode'] = 'PDF';
                if($Row['File'] > 0) $Invoice = '<a href="'.$this->System->Link('/file?id='.$Row['File']).'">'.$Row['BillCode'].'</a>';
                else $Invoice = NotBlank($Row['BillCode']);
                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()
  {
    $Finance = &$this->System->Modules['Finance'];
    $Finance->LoadMonthParameters(0);
    $this->System->Modules['Finance']->LoadTariffs(1);

    if(array_key_exists('Subject', $_GET))
    {
      if(!$this->System->User->CheckPermission('Finance', 'Manage')) return('Nemáte oprávnění');
      $DbResult = $this->Database->query('SELECT * FROM Subject WHERE Id='.$_GET['Subject']);
      if($DbResult->num_rows > 0)
        $Subject = $DbResult->fetch_assoc();
      else return($this->SystemMessage('Chyba', 'Subjekt nenalezen'));
    } else
    {
      if(!$this->System->User->CheckPermission('Finance', 'DisplaySubjectState')) return('Nemáte oprávnění');
      $UserId = $this->System->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();
        $DbResult = $this->Database->query('SELECT * FROM Subject WHERE Id=(SELECT Subject FROM Member WHERE Id='.$CustomerUserRel['Customer'].')');
        $Subject = $DbResult->fetch_assoc();
      } else return($this->SystemMessage('Chyba', 'Nejste zákazníkem'));
    }

    if(!array_key_exists('show', $_GET)) $_GET['show'] = '';
    $DbResult = $this->Database->query('SELECT Member.* '.
      'FROM Member LEFT JOIN MemberPayment ON MemberPayment.Member=Member.Id WHERE Member.Subject='.$Subject['Id']);
    if($DbResult->num_rows == 0)
    {
      return($this->SystemMessage('Položka nenalezena', 'Zákazník nenalezen'));
    }
    $Member = $DbResult->fetch_assoc();
    $Output = '<table width="100%" border="0" cellspacing="0" cellpadding="3"><tr><td valign="top">';

    // Account state
    $UserOperationTableQuery = '((SELECT Text, Time, Value AS 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 as 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);

    $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='.$Member['Id'].') AND (ServiceCustomerRel.Action 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);
  }
}