Changeset 720 for trunk/Modules/Finance


Ignore:
Timestamp:
Jan 3, 2015, 5:55:34 PM (10 years ago)
Author:
chronos
Message:
  • Modified: Invoice negative values replaced by Direction column which is used to determine if invoice is in or out invoice.
Location:
trunk/Modules/Finance
Files:
5 edited

Legend:

Unmodified
Added
Removed

  • trunk/Modules/Finance/Bill.php

    r719 r720  
    7373
    7474    // If negative value => switch sides
    75     if($Invoice['Value'] < 0)
     75    if($Invoice['Direction'] == -1)
    7676    {
    7777        $Subject = $SubjectTo;
    7878        $SubjectTo = $SubjectFrom;
    7979        $SubjectFrom = $Subject;
    80         foreach($InvoiceItems as $Index => $Item)
    81         {
    82                 $InvoiceItems[$Index]['Price'] = -$InvoiceItems[$Index]['Price'];
    83         }
    8480    }
    8581
    … …  
    159155    $MainSubjectAccount = $DbResult->fetch_assoc();
    160156
    161     //$Dodavka = array();
    162     //$DbResult = $this->Database->select('FinanceInvoiceItem', '*', 'FinanceInvoice='.$BillId);
    163     //while($Item = $DbResult->fetch_assoc())
    164     //{
    165     //  $InvoiceItems[$Item['Id']] = $Item;
    166     //}
    167157    $BooleanText = array('Ne', 'Ano');
    168158    if($Operation['Direction'] == -1)

  • trunk/Modules/Finance/Finance.php

    r719 r720  
    186186    while($Member = $DbResult->fetch_assoc())
    187187    {
    188       $DbResult2 = $this->Database->query('SELECT ((SELECT COALESCE(SUM(-Value*Direction), 0) FROM FinanceOperation '.
    189           'WHERE Subject='.$Member['Subject'].') + (SELECT COALESCE(SUM(-Value), 0) FROM FinanceInvoice '.
    190           'WHERE Subject='.$Member['Subject'].')) as Cash');
     188      $DbResult2 = $this->Database->query('SELECT ((SELECT COALESCE(-SUM(Value*Direction), 0) FROM FinanceOperation '.
     189          'WHERE Subject='.$Member['Subject'].') + (SELECT COALESCE(-SUM(Value*Direction), 0) FROM FinanceInvoice '.
     190          'WHERE Subject='.$Member['Subject'].')) AS Cash');
    191191      $Cash = $DbResult2->fetch_row();
    192192      $Cash = $Cash[0];
    … …  
    277277      'DefaultSortOrder' => 1,
    278278      'Items' => array(
    279         'Direction' => array('Type' => 'TFinanceDirection', 'Caption' => 'Směr', 'Default' => '1'),
     279        'Direction' => array('Type' => 'TFinanceOperationDirection', 'Caption' => 'Směr', 'Default' => '1'),
    280280        'DocumentLine' => array('Type' => 'TDocumentLine', 'Caption' => 'Dokladová řada', 'Default' => ''),
    281281        'BillCode' => array('Type' => 'String', 'Caption' => 'Označení', 'Default' => ''),
    … …  
    337337    $this->System->FormManager->Classes['FinanceAccountOut']['Items']['Treasury']['Hidden'] = true;
    338338   
    339     $this->System->FormManager->RegisterFormType('TFinanceDirection', array(
     339    $this->System->FormManager->RegisterFormType('TFinanceOperationDirection', array(
    340340      'Type' => 'Enumeration',
    341341      'States' => array(-1 => 'Výdej', 1 => 'Příjem'),
    … …  
    347347      'DefaultSortOrder' => 1,
    348348      'Items' => array(
    349         'DocumentLine' => array('Type' => 'TDocumentLine', 'Caption' => 'Dokladová řada', 'Default' => ''),
     349        'Direction' => array('Type' => 'TFinanceInvoiceDirection', 'Caption' => 'Směr', 'Default' => '1'),
     350        'DocumentLine' => array('Type' => 'TDocumentLine', 'Caption' => 'Dokladová řada', 'Default' => ''),
    350351        'BillCode' => array('Type' => 'String', 'Caption' => 'Označení', 'Default' => ''),
    351352        'Subject' => array('Type' => 'TSubject', 'Caption' => 'Subjekt', 'Default' => ''),
    … …  
    367368      'BeforeInsert' => array($this, 'BeforeInsertFinanceOperation'),
    368369    ));
     370    $this->System->FormManager->RegisterClass('FinanceInvoiceIn', $this->System->FormManager->Classes['FinanceInvoice']);
     371    $this->System->FormManager->Classes['FinanceInvoiceIn']['Title'] = 'Přijaté faktury';
     372    $this->System->FormManager->Classes['FinanceInvoiceIn']['Items']['Direction']['Default'] = -1;
     373    $this->System->FormManager->Classes['FinanceInvoiceIn']['Items']['Direction']['Hidden'] = true;
     374    $this->System->FormManager->Classes['FinanceInvoiceIn']['Items']['Direction']['Filter'] = true;
     375    $this->System->FormManager->Classes['FinanceInvoiceIn']['Items']['DocumentLine']['Default'] = DOC_LINE_INVOICE_IN;
     376    $this->System->FormManager->Classes['FinanceInvoiceIn']['Items']['DocumentLine']['Hidden'] = true;
     377    $this->System->FormManager->Classes['FinanceInvoiceIn']['Items']['DocumentLine']['Filter'] = true;
     378       
     379    $this->System->FormManager->RegisterClass('FinanceInvoiceOut', $this->System->FormManager->Classes['FinanceInvoice']);
     380    $this->System->FormManager->Classes['FinanceInvoiceOut']['Title'] = 'Vydané faktury';
     381    $this->System->FormManager->Classes['FinanceInvoiceOut']['Items']['Direction']['Default'] = 1;
     382    $this->System->FormManager->Classes['FinanceInvoiceOut']['Items']['Direction']['Hidden'] = true;
     383    $this->System->FormManager->Classes['FinanceInvoiceOut']['Items']['Direction']['Filter'] = true;
     384    $this->System->FormManager->Classes['FinanceInvoiceOut']['Items']['DocumentLine']['Default'] = DOC_LINE_INVOICE_OUT;
     385    $this->System->FormManager->Classes['FinanceInvoiceOut']['Items']['DocumentLine']['Hidden'] = true;
     386    $this->System->FormManager->Classes['FinanceInvoiceOut']['Items']['DocumentLine']['Filter'] = true;
     387
     388    $this->System->FormManager->RegisterFormType('TFinanceInvoiceDirection', array(
     389                'Type' => 'Enumeration',
     390                'States' => array(-1 => 'Příjem', 1 => 'Výdej'),
     391    ));
     392   
    369393    $this->System->FormManager->RegisterClass('Company', array(
    370394      'Title' => 'Firma',

  • trunk/Modules/Finance/Manage.php

    r719 r720  
    5050  {
    5151    if(!$this->System->User->CheckPermission('Finance', 'Manage')) return('Nemáte oprávnění');
    52     $SQL = 'SELECT Member.*, MemberPayment.MonthlyTotal AS Monthly, '.
    53       'MemberPayment.Cash AS Cash, '.
    54       '(SELECT GROUP_CONCAT(Service.Name) FROM ServiceCustomerRel LEFT JOIN Service '.
    55       'ON Service.Id=ServiceCustomerRel.Service WHERE ServiceCustomerRel.Customer=Member.Id AND '.
    56       'ServiceCustomerRel.Action IS NULL) AS ServicesNextMonth, '.
    57       'UNIX_TIMESTAMP(Member.BillingPeriodLastDate) AS LastDate, Subject.Name AS SubjectName, '.
    58       'FinanceBillingPeriod.Name AS BillingPeriodName '.
    59       'FROM MemberPayment JOIN Member ON Member.Id=MemberPayment.Member JOIN Subject '.
    60       'ON Subject.Id=Member.Subject LEFT JOIN FinanceBillingPeriod ON '.
    61       'FinanceBillingPeriod.Id=Member.BillingPeriodNext WHERE (Member.Blocked = 0)'.
    62       'AND (Member.BillingPeriodNext > 1) AND (MemberPayment.MonthlyTotal != 0)';
     52    $SQL = 'SELECT `Member`.*, `MemberPayment`.`MonthlyTotal` AS `Monthly`, '.
     53      '`MemberPayment`.`Cash` AS `Cash`, '.
     54      '(SELECT GROUP_CONCAT(`Service`.`Name`) FROM `ServiceCustomerRel` LEFT JOIN `Service` '.
     55      'ON `Service`.`Id`=`ServiceCustomerRel`.`Service` WHERE `ServiceCustomerRel`.`Customer`=`Member`.`Id` AND '.
     56      '`ServiceCustomerRel`.`Action` IS NULL) AS `ServicesNextMonth`, '.
     57      'UNIX_TIMESTAMP(`Member`.`BillingPeriodLastDate`) AS `LastDate`, `Subject`.`Name` AS `SubjectName`, '.
     58      '`FinanceBillingPeriod`.`Name` AS `BillingPeriodName` '.
     59      'FROM `MemberPayment` JOIN `Member` ON `Member`.`Id`=`MemberPayment`.`Member` JOIN `Subject` '.
     60      'ON `Subject`.`Id`=`Member`.`Subject` LEFT JOIN `FinanceBillingPeriod` ON '.
     61      '`FinanceBillingPeriod`.`Id`=`Member`.`BillingPeriodNext` WHERE (`Member`.`Blocked` = 0)'.
     62      'AND (`Member`.`BillingPeriodNext` > 1) AND (`MemberPayment`.`MonthlyTotal` != 0)';
    6363
    6464    $DbResult = $this->Database->query('SELECT COUNT(*) FROM ('.$SQL.') AS T');
    … …  
    100100  }
    101101
    102   function InsertInvoice($Subject, $TimeCreation, $TimeDue, $Items,
     102  function InsertInvoice($Subject, $TimeCreation, $TimeDue, $Direction, $Items,
    103103  $DocumentLine, $PeriodFrom, $PeriodTo)
    104104  {
    … …  
    109109    $SumValue = 0;
    110110    foreach($Items as $Item)
    111         $SumValue = $SumValue + ceil($Item['Price'] * $Item['Quantity']);
     111      $SumValue = $SumValue + ceil($Item['Price'] * $Item['Quantity']);   
    112112    $this->Database->insert('FinanceInvoice', array(
    113           'Subject' => $Subject, 'Time' => TimeToMysqlDateTime($TimeCreation),
    114           'TimeDue' => TimeToMysqlDateTime($TimeDue), 'Value' => $SumValue, 'BillCode' => $BillCode,
    115           'PeriodFrom' => TimeToMysqlDate($PeriodFrom), 'PeriodTo' => TimeToMysqlDate($PeriodTo),
    116           'Generate' => 1, 'DocumentLine' => $DocumentLine));
     113      'Subject' => $Subject, 'Time' => TimeToMysqlDateTime($TimeCreation),
     114      'TimeDue' => TimeToMysqlDateTime($TimeDue), 'Value' => $SumValue,
     115        'Direction' => $Direction, 'BillCode' => $BillCode,
     116      'PeriodFrom' => TimeToMysqlDate($PeriodFrom), 'PeriodTo' => TimeToMysqlDate($PeriodTo),
     117      'Generate' => 1, 'DocumentLine' => $DocumentLine));
    117118    $InvoiceId = $this->Database->insert_id;
    118119    foreach($Items as $Item)
    … …  
    130131
    131132    // Generuj účetní položky
    132     $DbResult = $this->Database->query('SELECT Member.*, MemberPayment.MonthlyTotal AS MonthlyTotal, '.
    133       'UNIX_TIMESTAMP(Member.BillingPeriodLastDate), Subject.Name AS SubjectName,'.
    134       'MemberPayment.MonthlyPlus AS MonthlyPlus '.
    135       'FROM MemberPayment JOIN Member ON Member.Id=MemberPayment.Member '.
    136       'JOIN Subject ON Subject.Id=Member.Subject');
     133    $DbResult = $this->Database->query('SELECT `Member`.*, `MemberPayment`.`MonthlyTotal` AS `MonthlyTotal`, '.
     134      'UNIX_TIMESTAMP(`Member`.`BillingPeriodLastDate`), `Subject`.`Name` AS `SubjectName`,'.
     135      '`MemberPayment`.`MonthlyPlus` AS `MonthlyPlus` '.
     136      'FROM `MemberPayment` JOIN `Member` ON `Member`.`Id`=`MemberPayment`.`Member` '.
     137      'JOIN `Subject` ON `Subject`.`Id`=`Member`.`Subject`');
    137138    while($Member = $DbResult->fetch_assoc())
    138139    {
    … …  
    163164        }
    164165        $PayPerPeriod = $MonthlyTotal * $Period['MonthCount'];
     166        // We can't produce negative invoice except storno invoice.
     167        // TODO: In case of negative invoice it is not sufficient to reverse invoicing direction 
     168        // Other subject should invoice only possitive items. Negative items should be somehow removed.
     169        if($MonthlyTotal >= 0)
     170                $Direction = 1; // Standard out invoice
     171          else {
     172                $Direction = -1; // In case of negative total value generate reverse invoice for other subject
     173                foreach($InvoiceItems as $Index => $Item)
     174                        $InvoiceItems[$Index]['Price'] = -$Item['Price'];
     175          }
     176       
     177       
    165178        if($PayPerPeriod != 0)
    166179        {
    … …  
    168181          $Output .= $TimePeriodText.': '.$MonthlyTotal.' * '.$Period['MonthCount'].' = '.$PayPerPeriod.'<br />';
    169182          $this->InsertInvoice($Member['Subject'], time(), time() + 3600 * 24 * INVOICE_DUE_DAYS,
    170             $InvoiceItems, DOC_LINE_INVOICE_OUT, $Period['From'], $Period['To']);
     183            $Direction, $InvoiceItems, DOC_LINE_INVOICE_OUT, $Period['From'], $Period['To']);
    171184
    172185          $Output .= $this->SendPaymentEmail($Member['Id']);
    … …  
    227240    $Output .= $Finance->LoadTariffs();
    228241    $Output .= $Finance->RecalculateMemberPayment();
    229 
    230     // Celkovy prehled
    231     $DbResult = $this->Database->query('SELECT SUM(Product.SellPrice) AS Price FROM StockSerialNumber '.
    232       'JOIN Product ON StockSerialNumber.Product = Product.Id WHERE (StockSerialNumber.TimeElimination IS NULL) AND (StockSerialNumber.Segment IS NOT NULL)');
    233     $Row = $DbResult->fetch_row();
    234     $TotalDeviceCost = $Row[0];
    235     $Output .= 'Celková cena zařízení: Zařízení('.$TotalDeviceCost;
    236242
    237243    $DbResult = $this->Database->query('SELECT SUM(NetworkDevice) FROM MemberPayment');
    … …  
    279285      $Output .= 'Aktualizuji služby....<br>';
    280286      $this->TableUpdatePeriod('Service');
     287     
    281288      // Update customer service selections
    282289      $Output .= 'Aktualizuji výběr služeb zákazníků....<br>';
    … …  
    343350        '<th style="border-style: solid; border-width: 1px; padding: 1px 5px 1px 5px; text-align: center; font-weight: bold;">Částka [Kč]</th></tr>'."\n";
    344351      $DbResult = $this->Database->query('SELECT T1.* FROM ((SELECT Text, Time, (Value*Direction) AS Value, File FROM FinanceOperation WHERE (Subject='.$Member['Subject'].')) UNION ALL '.
    345         '(SELECT CONCAT(`Text`, (SELECT GROUP_CONCAT(`Description` SEPARATOR "<br/>") FROM `FinanceInvoiceItem` WHERE `FinanceInvoiceItem`.`FinanceInvoice` = `FinanceInvoice`.`Id`)) AS `Text`, Time, -Value as Value, File FROM FinanceInvoice WHERE (Subject='.
     352        '(SELECT CONCAT(`Text`, (SELECT GROUP_CONCAT(`Description` SEPARATOR "<br/>") FROM `FinanceInvoiceItem` WHERE `FinanceInvoiceItem`.`FinanceInvoice` = `FinanceInvoice`.`Id`)) AS `Text`, Time, -(Value*Direction) as Value, File FROM FinanceInvoice WHERE (Subject='.
    346353        $Member['Subject'].')) ORDER BY Time DESC) AS T1 WHERE (T1.Time > "'.$Member['BillingPeriodLastDate'].'")');
    347354      while($DbRow = $DbResult->fetch_assoc())

  • trunk/Modules/Finance/UserState.php

    r719 r720  
    1212          'FROM FinanceOperation WHERE (Subject='.$Subject['Id'].')) UNION ALL '.
    1313                '(SELECT (SELECT GROUP_CONCAT(Description SEPARATOR ",") FROM FinanceInvoiceItem WHERE FinanceInvoice=FinanceInvoice.Id) AS Text, '.
    14                 'Time, -Value as Value, File, BillCode, PeriodFrom, PeriodTo FROM FinanceInvoice WHERE (Subject='.$Subject['Id'].')))';
     14                'Time, -(Value*Direction) as Value, File, BillCode, PeriodFrom, PeriodTo FROM FinanceInvoice WHERE (Subject='.$Subject['Id'].')))';
    1515
    1616    $Output = '<div style="text-align:center">Výpis finančních operací</div>';
    … …  
    104104      'FROM FinanceOperation WHERE (Subject='.$Subject['Id'].')) UNION ALL '.
    105105      '(SELECT (SELECT GROUP_CONCAT(Description SEPARATOR ",") FROM FinanceInvoiceItem WHERE FinanceInvoice=FinanceInvoice.Id) AS Text, '.
    106       'Time, -Value as Value, File, BillCode, PeriodFrom, PeriodTo FROM FinanceInvoice WHERE (Subject='.$Subject['Id'].')) ORDER BY Time DESC) AS T1';
     106      'Time, -(Value*Direction) as Value, File, BillCode, PeriodFrom, PeriodTo FROM FinanceInvoice WHERE (Subject='.$Subject['Id'].')) ORDER BY Time DESC) AS T1';
    107107    $DbResult = $this->Database->query('SELECT SUM(T1.Value) AS Total FROM '.$UserOperationTableQuery);
    108108    $DbRow = $DbResult->fetch_array();

  • trunk/Modules/Finance/Zivnost.php

    r719 r720  
    2727    $Balance['Spend']['End'] = $Row[0] + 0;
    2828
    29     $DbResult = $this->Database->query('SELECT SUM(Value) FROM FinanceInvoice WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Value > 0)');
     29    $DbResult = $this->Database->query('SELECT SUM(Value*Direction) FROM FinanceInvoice WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Direction = 1)');
    3030    $Row = $DbResult->fetch_array();
    3131    $Balance['Claims']['Start'] = $Row[0] + 0;
    32     $DbResult = $this->Database->query('SELECT SUM(Value) FROM FinanceInvoice WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Value > 0)');
     32    $DbResult = $this->Database->query('SELECT SUM(Value*Direction) FROM FinanceInvoice WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Direction = 1)');
    3333    $Row = $DbResult->fetch_array();
    3434    $Balance['Claims']['End'] = $Row[0] + 0;
    3535
    36     $DbResult = $this->Database->query('SELECT SUM(-Value) FROM FinanceInvoice WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Value < 0)');
     36    $DbResult = $this->Database->query('SELECT -SUM(Value*Direction) FROM FinanceInvoice WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Direction = -1)');
    3737    $Row = $DbResult->fetch_array();
    3838    $Balance['Liabilities']['Start'] = $Row[0] + 0;
    39     $DbResult = $this->Database->query('SELECT SUM(-Value) FROM FinanceInvoice WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Value < 0)');
     39    $DbResult = $this->Database->query('SELECT -SUM(Value*Direction) FROM FinanceInvoice WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Direction = -1)');
    4040    $Row = $DbResult->fetch_array();
    4141    $Balance['Liabilities']['End'] = $Row[0] + 0;
    … …  
    206206        $DbResult = $this->Database->query('SELECT *, (SELECT GROUP_CONCAT(Description SEPARATOR ",") FROM FinanceInvoiceItem WHERE FinanceInvoiceItem.FinanceInvoice = FinanceInvoice.Id) AS Text '.
    207207          'FROM FinanceInvoice JOIN Subject ON Subject.Id = FinanceInvoice.Subject '.
    208           'WHERE (Value > 0) AND (FinanceInvoice.Time >= "'.$Year['DateStart'].
     208          'WHERE (Direction = 1) AND (FinanceInvoice.Time >= "'.$Year['DateStart'].
    209209          '") AND (FinanceInvoice.Time <= "'.$Year['DateEnd'].'") ORDER BY Time');
    210210        while($Row = $DbResult->fetch_array())
    211211        {
    212212          if($Row['TimePayment'] == '0000-00-00 00:00:00') $Row['TimePayment'] = '&nbsp;';
    213           $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.$Row['BillCode'].'</td><td>'.$Row['Name'].'</td><td>'.$Row['Text'].'</td><td>'.$Row['Value'].'</td></tr>';
     213          $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.$Row['BillCode'].
     214            '</td><td>'.$Row['Name'].'</td><td>'.$Row['Text'].'</td><td>'.($Row['Value'] * $Row['Direction']).'</td></tr>';
    214215          $Total += $Row['Value'];
    215216        }
    … …  
    228229        $DbResult = $this->Database->query('SELECT *, (SELECT GROUP_CONCAT(Description SEPARATOR ",") FROM FinanceInvoiceItem WHERE FinanceInvoiceItem.FinanceInvoice = FinanceInvoice.Id) AS Text '.
    229230          'FROM FinanceInvoice JOIN Subject ON Subject.Id = FinanceInvoice.Subject '.
    230           'WHERE (Value < 0) AND (FinanceInvoice.Time >= "'.$Year['DateStart'].
     231          'WHERE (Direction = -1) AND (FinanceInvoice.Time >= "'.$Year['DateStart'].
    231232          '") AND (FinanceInvoice.Time <= "'.$Year['DateEnd'].'") ORDER BY Time');
    232233        while($Row = $DbResult->fetch_array())
    … …  
    234235          if($Row['TimePayment'] == '0000-00-00 00:00:00') $Row['TimePayment'] = '&nbsp;';
    235236          $Row['Value'] = $Row['Value'] * -1;
    236           $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.$Row['BillCode'].'</td><td>'.$Row['Name'].'</td><td>'.$Row['Text'].'</td><td>'.$Row['Value'].'</td></tr>';         
     237          $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.$Row['BillCode'].
     238            '</td><td>'.$Row['Name'].'</td><td>'.$Row['Text'].'</td><td>'.($Row['Value'] * $Row['Direction']).'</td></tr>';         
    237239          $Total += $Row['Value'];
    238240        }
    … …  
    245247        $Output .= '<tr><th>Jméno</th><th>Závazky [Kč]</th><th>Pohledávky [Kč]</th><th>Příjmy [Kč]</th><th>Výdaje [Kč]</th><th>Stav účtu</th><th>Starý systém</th></tr>';
    246248        $DbResult = $this->Database->query('SELECT Id, Name, 0 AS Cash, '.
    247         '(SELECT SUM(T1.Value) FROM FinanceInvoice AS T1 WHERE (T1.Subject = Subject.Id) AND (T1.Value > 0)) as Claims, '.
    248         '(SELECT SUM(T2.Value) FROM FinanceInvoice AS T2 WHERE (T2.Subject = Subject.Id) AND (T2.Value > 0) AND (TimePayment IS NULL)) as OpenedClaims, '.
    249         '(SELECT -SUM(T3.Value) FROM FinanceInvoice AS T3 WHERE (T3.Subject = Subject.Id) AND (T3.Value  < 0)) as Liabilities, '.
    250         '(SELECT -SUM(T4.Value) FROM FinanceInvoice AS T4 WHERE (T4.Subject = Subject.Id) AND (T4.Value < 0) AND (TimePayment IS NULL)) AS OpenedLiabilities, '.
     249        '(SELECT SUM(T1.Value*T1.Direction) FROM FinanceInvoice AS T1 WHERE (T1.Subject = Subject.Id) AND (T1.Direction = 1)) as Claims, '.
     250        '(SELECT SUM(T2.Value*T2.Direction) FROM FinanceInvoice AS T2 WHERE (T2.Subject = Subject.Id) AND (T2.Direction = 1) AND (TimePayment IS NULL)) as OpenedClaims, '.
     251        '(SELECT -SUM(T3.Value*T3.Direction) FROM FinanceInvoice AS T3 WHERE (T3.Subject = Subject.Id) AND (T3.Direction = -1)) as Liabilities, '.
     252        '(SELECT -SUM(T4.Value*T4.Direction) FROM FinanceInvoice AS T4 WHERE (T4.Subject = Subject.Id) AND (T4.Direction = -1) AND (TimePayment IS NULL)) AS OpenedLiabilities, '.
    251253        '(SELECT SUM(T5.Value*T5.Direction) FROM FinanceOperation AS T5 WHERE (T5.Subject = Subject.Id) AND (T5.Direction = 1)) AS Gains, '.
    252254        '(SELECT SUM(T6.Value*T6.Direction) FROM FinanceOperation AS T6 WHERE (T6.Subject = Subject.Id) AND (T6.Direction = -1)) AS Spends '.
    … …  
    287289        $Output .= '<table style="font-size: smaller;" border="1" cellspacing="0" cellpadding="3">';
    288290        $Output .= '<tr><th>Datum vytvoření</th><th>Datum zaplacení</th><th>Název</th><th>Hodnota [Kč]</th><th>Doklad</th></tr>';
    289         $DbResult = $this->Database->select('FinanceInvoice', '*, (SELECT GROUP_CONCAT(Description SEPARATOR ",") FROM FinanceInvoiceItem WHERE FinanceInvoiceItem.FinanceInvoice = FinanceInvoice.Id) AS Text', 'Subject='.$_GET['Id'].' ORDER BY Time');
     291        $DbResult = $this->Database->select('FinanceInvoice', '*, (SELECT GROUP_CONCAT(Description SEPARATOR ",") '.
     292                'FROM FinanceInvoiceItem WHERE FinanceInvoiceItem.FinanceInvoice = FinanceInvoice.Id) AS Text',
     293                'Subject='.$_GET['Id'].' ORDER BY Time');
    290294        while($Row = $DbResult->fetch_array())
    291295        {
    292296          $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.HumanDate($Row['TimePayment']).
    293             '</td><td>'.$Row['Text'].'</td><td>'.($Row['Value']).'</td><td>'.$Row['BillCode'].'</td></tr>';
     297            '</td><td>'.$Row['Text'].'</td><td>'.ceil($Row['Value'] * $Row['Direction']).'</td><td>'.$Row['BillCode'].'</td></tr>';
    294298        }
    295299        $Output .= '</table></td></tr></table>';
    … …  
    305309        $Output .= '</table>';*/
    306310
    307         $DbResult = $this->Database->query('SELECT Id, 0 AS Cash, (SELECT SUM(FinanceInvoice.Value) '.
    308           'FROM FinanceInvoice WHERE FinanceInvoice.Subject = Subject.Id AND '.
    309           'FinanceInvoice.Value > 0) as Claims, (SELECT SUM(FinanceInvoice.Value) '.
    310           'FROM FinanceInvoice WHERE FinanceInvoice.Subject = Subject.Id AND '.
    311           'FinanceInvoice.Value > 0 AND TimePayment IS NULL) as OpenedClaims, '.
    312           '(SELECT SUM(FinanceInvoice.Value) FROM FinanceInvoice '.
    313           'WHERE FinanceInvoice.Subject = Subject.Id AND FinanceInvoice.Value < 0) AS '.
     311        $DbResult = $this->Database->query('SELECT Id, 0 AS Cash, (SELECT SUM(FinanceInvoice.Value * FinanceInvoice.Direction) '.
     312          'FROM FinanceInvoice WHERE (FinanceInvoice.Subject = Subject.Id) AND '.
     313          '(FinanceInvoice.Direction = 1)) as Claims, (SELECT SUM(FinanceInvoice.Value * FinanceInvoice.Direction) '.
     314          'FROM FinanceInvoice WHERE (FinanceInvoice.Subject = Subject.Id) AND ('.
     315          'FinanceInvoice.Direction = 1) AND (TimePayment IS NULL)) as OpenedClaims, '.
     316          '(SELECT SUM(FinanceInvoice.Value * FinanceInvoice.Direction) FROM FinanceInvoice '.
     317          'WHERE (FinanceInvoice.Subject = Subject.Id) AND (FinanceInvoice.Direction = -1)) AS '.
    314318          'Liabilities, (SELECT SUM(FinanceInvoice.Value) FROM FinanceInvoice '.
    315           'WHERE FinanceInvoice.Subject = Subject.Id AND FinanceInvoice.Value < 0 '.
    316           'AND TimePayment IS NULL) as OpenedLiabilities, (SELECT SUM(FinanceOperation.Value*FinanceOperation.Direction) '.
     319          'WHERE (FinanceInvoice.Subject = Subject.Id) AND (FinanceInvoice.Direction = -1) '.
     320          'AND (TimePayment IS NULL)) AS OpenedLiabilities, '.
     321                '(SELECT SUM(FinanceOperation.Value*FinanceOperation.Direction) '.
    317322          'FROM FinanceOperation WHERE FinanceOperation.Subject = Subject.Id AND FinanceOperation.Direction = 1) '.
    318323          'AS Gains, (SELECT SUM(FinanceOperation.Value*FinanceOperation.Direction) FROM FinanceOperation WHERE '.
Note: See TracChangeset for help on using the changeset viewer.