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.
File:
1 edited

Legend:

Unmodified
Added
Removed

  • 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.