source: trunk/finance/user_state.php@ 508

<?php

include_once('../Common/Global.php');

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

  function Show()
  {
    $Finance = &$this->System->Modules['Finance'];
    $this->System->Modules['Finance']->LoadTariffs(1);

    if(array_key_exists('Subject', $_GET))
    {
      if(!$this->System->Modules['User']->CheckPermission('Finance', 'Manage')) return('Nemáte oprávnění');
      $DbResult = $this->Database->query('SELECT * FROM Subject WHERE Id='.$_GET['Subject']);
      $Subject = $DbResult->fetch_assoc();
    } else
    {
      if(!$this->System->Modules['User']->CheckPermission('Finance', 'DisplaySubjectState')) return('Nemáte oprávnění');
      $UserId = $this->System->Modules['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.*, MemberPayment.MonthlyPlus AS MonthlyPlus '.
      'FROM Member LEFT JOIN MemberPayment ON MemberPayment.Member=Member.Id WHERE Member.Subject='.$Subject['Id']);
    $Member = $DbResult->fetch_assoc();
    $Output = '<table width="100%" border="0" cellspacing="0" cellpadding="3"><tr><td valign="top">';

    if(array_key_exists('year', $_GET))
    {
      $DbResult = $this->Database->query('SELECT * FROM FinanceYear WHERE Id='.addslashes($_GET['year'] * 1));
    } else 
    {
      $DbResult = $this->Database->query('SELECT * FROM FinanceYear ORDER BY Year DESC LIMIT 1');
    }
    $FinanceYearSelected = $DbResult->fetch_assoc();
    $YearWhere = ' WHERE (Time >= "'.$FinanceYearSelected['DateStart'].'") AND (Time <= "'.$FinanceYearSelected['DateEnd'].'")';
   
    // Year list
    if(array_key_exists('Subject', $_GET)) $SubjectParam = '&amp;Subject='.($_GET['Subject'] * 1);
      else $SubjectParam = '';
    $Output .= 'Roky: ';
    $DbResult = $this->Database->query('SELECT * FROM FinanceYear WHERE Year >= '.
      substr($Member['MembershipDate'], 0, strpos($Member['MembershipDate'], '-')));
    while($FinanceYear = $DbResult->fetch_assoc())
    {
      $Year = '<a href="?year='.$FinanceYear['Id'].$SubjectParam.'">'.$FinanceYear['Year'].'</a> ';
      if($FinanceYear['Id'] == $FinanceYearSelected['Id']) $Output .= '<strong>'.$Year.'</strong>';
        else $Output .= $Year;
    }
    $Output .= '<br/><br/>';   
    
    // 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, '.
      'TimeCreation as 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 /><br/>';
    
    // Tabulka operaci
    $Output .= 'Výpis operací pro subjekt '.$Subject['Name'].':<br />';
    $Output .= '<table style="font-size: smaller;" class="WideTable"><tr>'.
    '<th>Datum</th><th>Popis</th><th>Částka</th><th>Období</th><th>Doklad</th></tr>';
    $Operations = array();
    $DbResult = $this->Database->query('SELECT T1.* FROM '.$UserOperationTableQuery.$YearWhere);
    while($DbRow = $DbResult->fetch_array())
      $Operations[] = $DbRow;
    $SumValue = 0;
    foreach($Operations as $Row)
    {
      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 = '&nbsp;';
      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: center;">'.$Period.'</td>'.
      '<td style="text-align: center;">'.$Invoice.'</td></tr>';
      $SumValue = $SumValue + $Row['Value'];
    }
    $Output .= '<tr><td style="text-align: right;" colspan="2"><strong>Celkem za rok</strong></td>'.
    '<td style="text-align: right;"><strong>'.round($SumValue, 2).'</strong></td>'.
    '<td style="text-align: center;">&nbsp;</td>'.
    '<td style="text-align: center;">&nbsp;</td></tr>';
    
    $Output .= '</table>';
    $Output .= '</td><td valign="top">';

    $Output .= 'Variabilní symbol pro platby: <strong>'.$Subject['Id'].'</strong><br><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'];
    }
    
    if($Member['MonthlyPlus'] != 0)
    {
      $Output .= '<tr><td>Příjem za spotřebu</td><td>'.(-$Member['MonthlyPlus']).'</td></tr>';
      $Total -= $Member['MonthlyPlus'];
    }
    $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 />';
    
    if($Member['MonthlyPlus'] != 0)
    {
      $Output .= 'Měsíční příjem za spotřebu zařízení umístěných u uživatele:<br>';
      $Output .= '<table class="WideTable"><tr><th>Název zařízení</th><th>Spotřeba [W]</th><th>Příjem [Kč]</th></tr>';
      $DbResult = $this->Database->query('SELECT Product.Name AS Name, Product.Consumption AS Consumption '.
        'FROM StockItem JOIN Product ON Product.Id = StockItem.Product '.
        'WHERE (StockItem.Location='.$Member['Id'].') AND (StockItem.TimeElimination IS NULL)');
      $TotalW = 0;
      $TotalPrice = 0;
      while($Row = $DbResult->fetch_assoc())
      {
        $Output .= '<tr><td>'.$Row['Name'].'</td><td>'.$Row['Consumption'].'</td><td>'.$this->System->Modules['Finance']->W2Kc($Row['Consumption']).'</td></tr>';
        $TotalW += $Row['Consumption'];
        $TotalPrice += $this->System->Modules['Finance']->W2Kc($Row['Consumption']);
      }
      $Output .= '<tr><td><strong>Celkem</strong></td><td><strong>'.$TotalW.'</strong></td><td><strong>'.$TotalPrice.'</strong></td></tr>';
      $Output .= '</table>';
  }
    $Output .= '</td></tr></table>';
    return($Output);
  }
}

$System->AddModule(new FinanceUserState());
$System->Modules['FinanceUserState']->GetOutput();

?>