Ignore:
Timestamp:
Jul 3, 2015, 11:36:07 PM (9 years ago)
Author:
chronos
Message:

to group id instead of DocumentLine.

File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/Modules/Finance/Zivnost.php

    r738 r740  
    1111  {
    1212    $Balance = array();
    13     $DbResult = $this->Database->query('SELECT SUM(Value) FROM FinanceOperation WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Taxable = 1) AND (Direction = 1)');
     13    $DbResult = $this->Database->query('SELECT SUM(Value) FROM FinanceOperation WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Taxable = 1) AND (ValueSign = 1)');
    1414    $Row = $DbResult->fetch_array();
    1515    $Balance['Income']['Start'] = $Row[0] + 0;
    16     $DbResult = $this->Database->query('SELECT SUM(Value) FROM FinanceOperation WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Taxable = 1) AND (Direction = 1)');
     16    $DbResult = $this->Database->query('SELECT SUM(Value) FROM FinanceOperation WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Taxable = 1) AND (ValueSign = 1)');
    1717    //echo('SELECT SUM(Value) FROM FinanceCashFlow WHERE Time <= "'.TimeToMysqlDateTime($EndTime).'" AND Value > 0 AND Taxable = 1'.'<br />');
    1818    $Row = $DbResult->fetch_array();
    1919    $Balance['Income']['End'] = $Row[0] + 0;
    2020
    21     $DbResult = $this->Database->query('SELECT -SUM(Value*Direction) FROM FinanceOperation WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Taxable = 1) AND (Direction = -1)');
     21    $DbResult = $this->Database->query('SELECT -SUM(Value*ValueSign) FROM FinanceOperation WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Taxable = 1) AND (ValueSign = -1)');
    2222    $Row = $DbResult->fetch_array();
    2323    $Balance['Spend']['Start'] = $Row[0] + 0;
    24     $DbResult = $this->Database->query('SELECT -SUM(Value*Direction) FROM FinanceOperation WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Taxable = 1) AND (Direction = -1)');
     24    $DbResult = $this->Database->query('SELECT -SUM(Value*ValueSign) FROM FinanceOperation WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Taxable = 1) AND (ValueSign = -1)');
    2525          //echo('SELECT -SUM(Value) FROM FinanceCashFlow WHERE Time <= "'.TimeToMysqlDateTime($EndTime).'" AND Value < 0 AND Taxable = 1'.'<br />');
    2626    $Row = $DbResult->fetch_array();
    2727    $Balance['Spend']['End'] = $Row[0] + 0;
    2828
    29     $DbResult = $this->Database->query('SELECT SUM(Value*Direction) FROM FinanceInvoice WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Direction = 1)');
     29    $DbResult = $this->Database->query('SELECT SUM(Value*ValueSign) FROM FinanceInvoice WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (ValueSign = 1)');
    3030    $Row = $DbResult->fetch_array();
    3131    $Balance['Claims']['Start'] = $Row[0] + 0;
    32     $DbResult = $this->Database->query('SELECT SUM(Value*Direction) FROM FinanceInvoice WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Direction = 1)');
     32    $DbResult = $this->Database->query('SELECT SUM(Value*ValueSign) FROM FinanceInvoice WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (ValueSign = 1)');
    3333    $Row = $DbResult->fetch_array();
    3434    $Balance['Claims']['End'] = $Row[0] + 0;
    3535
    36     $DbResult = $this->Database->query('SELECT -SUM(Value*Direction) FROM FinanceInvoice WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Direction = -1)');
     36    $DbResult = $this->Database->query('SELECT -SUM(Value*ValueSign) FROM FinanceInvoice WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (ValueSign = -1)');
    3737    $Row = $DbResult->fetch_array();
    3838    $Balance['Liabilities']['Start'] = $Row[0] + 0;
    39     $DbResult = $this->Database->query('SELECT -SUM(Value*Direction) FROM FinanceInvoice WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Direction = -1)');
     39    $DbResult = $this->Database->query('SELECT -SUM(Value*ValueSign) FROM FinanceInvoice WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (ValueSign = -1)');
    4040    $Row = $DbResult->fetch_array();
    4141    $Balance['Liabilities']['End'] = $Row[0] + 0;
    … …  
    144144        $Output .= '<tr><th>Čas</th><th>Kód</th><th>Subjekt</th><th>Text</th><th>Hodnota [Kč]</th><th>Daňový</th><th>Hotovost</th></tr>';
    145145        $DbResult = $this->Database->query('SELECT * FROM FinanceOperation LEFT JOIN Subject ON Subject.Id = FinanceOperation.Subject '.
    146           'WHERE (Direction = 1) AND (FinanceOperation.Time >= "'.$Year['DateStart'].'") AND (FinanceOperation.Time <= "'.$Year['DateEnd'].'") ORDER BY Time');
     146          'WHERE (ValueSign = 1) AND (FinanceOperation.Time >= "'.$Year['DateStart'].'") AND (FinanceOperation.Time <= "'.$Year['DateEnd'].'") ORDER BY Time');
    147147        while($Row = $DbResult->fetch_array())
    148148        {
    … …  
    174174        $Output .= '<tr><th>Čas</th><th>Kód</th><th>Subjekt</th><th>Text</th><th>Hodnota [Kč]</th><th>Daňový</th><th>Hotovost</th></tr>';
    175175        $DbResult = $this->Database->query('SELECT * FROM FinanceOperation LEFT JOIN Subject ON Subject.Id = FinanceOperation.Subject '.
    176           'WHERE (Direction = -1) AND (FinanceOperation.Time >= "'.$Year['DateStart'].'") AND (FinanceOperation.Time <= "'.$Year['DateEnd'].'") ORDER BY Time');
     176          'WHERE (ValueSign = -1) AND (FinanceOperation.Time >= "'.$Year['DateStart'].'") AND (FinanceOperation.Time <= "'.$Year['DateEnd'].'") ORDER BY Time');
    177177        while($Row = $DbResult->fetch_array())
    178178        {
    … …  
    204204        $DbResult = $this->Database->query('SELECT *, (SELECT GROUP_CONCAT(Description SEPARATOR ",") FROM FinanceInvoiceItem WHERE FinanceInvoiceItem.FinanceInvoice = FinanceInvoice.Id) AS Text '.
    205205          'FROM FinanceInvoice JOIN Subject ON Subject.Id = FinanceInvoice.Subject '.
    206           'WHERE (Direction = 1) AND (FinanceInvoice.Time >= "'.$Year['DateStart'].
     206          'WHERE (ValueSign = 1) AND (FinanceInvoice.Time >= "'.$Year['DateStart'].
    207207          '") AND (FinanceInvoice.Time <= "'.$Year['DateEnd'].'") ORDER BY Time');
    208208        while($Row = $DbResult->fetch_array())
    … …  
    210210          if($Row['TimePayment'] == '0000-00-00 00:00:00') $Row['TimePayment'] = '&nbsp;';
    211211          $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.$Row['BillCode'].
    212             '</td><td>'.$Row['Name'].'</td><td>'.$Row['Text'].'</td><td>'.($Row['Value'] * $Row['Direction']).'</td></tr>';
     212            '</td><td>'.$Row['Name'].'</td><td>'.$Row['Text'].'</td><td>'.($Row['Value'] * $Row['ValueSign']).'</td></tr>';
    213213          $Total += $Row['Value'];
    214214        }
    … …  
    227227        $DbResult = $this->Database->query('SELECT *, (SELECT GROUP_CONCAT(Description SEPARATOR ",") FROM FinanceInvoiceItem WHERE FinanceInvoiceItem.FinanceInvoice = FinanceInvoice.Id) AS Text '.
    228228          'FROM FinanceInvoice JOIN Subject ON Subject.Id = FinanceInvoice.Subject '.
    229           'WHERE (Direction = -1) AND (FinanceInvoice.Time >= "'.$Year['DateStart'].
     229          'WHERE (ValueSign = -1) AND (FinanceInvoice.Time >= "'.$Year['DateStart'].
    230230          '") AND (FinanceInvoice.Time <= "'.$Year['DateEnd'].'") ORDER BY Time');
    231231        while($Row = $DbResult->fetch_array())
    … …  
    234234          $Row['Value'] = $Row['Value'] * -1;
    235235          $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.$Row['BillCode'].
    236             '</td><td>'.$Row['Name'].'</td><td>'.$Row['Text'].'</td><td>'.($Row['Value'] * $Row['Direction']).'</td></tr>';
     236            '</td><td>'.$Row['Name'].'</td><td>'.$Row['Text'].'</td><td>'.($Row['Value'] * $Row['ValueSign']).'</td></tr>';
    237237          $Total += $Row['Value'];
    238238        }
    … …  
    245245        $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>';
    246246        $DbResult = $this->Database->query('SELECT Id, Name, 0 AS Cash, '.
    247         '(SELECT SUM(T1.Value*T1.Direction) FROM FinanceInvoice AS T1 WHERE (T1.Subject = Subject.Id) AND (T1.Direction = 1)) as Claims, '.
    248         '(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, '.
    249         '(SELECT -SUM(T3.Value*T3.Direction) FROM FinanceInvoice AS T3 WHERE (T3.Subject = Subject.Id) AND (T3.Direction = -1)) as Liabilities, '.
    250         '(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, '.
    251         '(SELECT SUM(T5.Value*T5.Direction) FROM FinanceOperation AS T5 WHERE (T5.Subject = Subject.Id) AND (T5.Direction = 1)) AS Gains, '.
    252         '(SELECT SUM(T6.Value*T6.Direction) FROM FinanceOperation AS T6 WHERE (T6.Subject = Subject.Id) AND (T6.Direction = -1)) AS Spends '.
     247        '(SELECT SUM(T1.Value*T1.ValueSign) FROM FinanceInvoice AS T1 WHERE (T1.Subject = Subject.Id) AND (T1.ValueSign = 1)) as Claims, '.
     248        '(SELECT SUM(T2.Value*T2.ValueSign) FROM FinanceInvoice AS T2 WHERE (T2.Subject = Subject.Id) AND (T2.ValueSign = 1) AND (TimePayment IS NULL)) as OpenedClaims, '.
     249        '(SELECT -SUM(T3.Value*T3.ValueSign) FROM FinanceInvoice AS T3 WHERE (T3.Subject = Subject.Id) AND (T3.ValueSign = -1)) as Liabilities, '.
     250        '(SELECT -SUM(T4.Value*T4.ValueSign) FROM FinanceInvoice AS T4 WHERE (T4.Subject = Subject.Id) AND (T4.ValueSign = -1) AND (TimePayment IS NULL)) AS OpenedLiabilities, '.
     251        '(SELECT SUM(T5.Value*T5.ValueSign) FROM FinanceOperation AS T5 WHERE (T5.Subject = Subject.Id) AND (T5.ValueSign = 1)) AS Gains, '.
     252        '(SELECT SUM(T6.Value*T6.ValueSign) FROM FinanceOperation AS T6 WHERE (T6.Subject = Subject.Id) AND (T6.ValueSign = -1)) AS Spends '.
    253253        'FROM Subject ORDER BY Name');
    254254        while($Row = $DbResult->fetch_assoc())
    … …  
    280280        {
    281281          $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.$Row['Text'].
    282             '</td><td>'.($Row['Value']*$Row['Direction']).'</td><td>'.$Row['BillCode'].'</td></tr>';
     282            '</td><td>'.($Row['Value']*$Row['ValueSign']).'</td><td>'.$Row['BillCode'].'</td></tr>';
    283283        }
    284284        $Output .= '</table></td><td style="vertical-align: top;">';
    … …  
    293293        {
    294294          $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.HumanDate($Row['TimePayment']).
    295             '</td><td>'.$Row['Text'].'</td><td>'.ceil($Row['Value'] * $Row['Direction']).'</td><td>'.$Row['BillCode'].'</td></tr>';
     295            '</td><td>'.$Row['Text'].'</td><td>'.ceil($Row['Value'] * $Row['ValueSign']).'</td><td>'.$Row['BillCode'].'</td></tr>';
    296296        }
    297297        $Output .= '</table></td></tr></table>';
    … …  
    303303        while($Row = $DbResult->fetch_array())
    304304        {
    305           $Output .= '<tr><td>'.$Row['Time'].'</td><td>'.$Row['TimePass'].'</td><td>'.$Row['Direction'].'</td><td>'.($Row['Value']).'</td><td>'.$Row['CashFlowId'].'</td></tr>';
     305          $Output .= '<tr><td>'.$Row['Time'].'</td><td>'.$Row['TimePass'].'</td><td>'.$Row['ValueSign'].'</td><td>'.($Row['Value']).'</td><td>'.$Row['CashFlowId'].'</td></tr>';
    306306        }
    307307        $Output .= '</table>';*/
    308308
    309         $DbResult = $this->Database->query('SELECT Id, 0 AS Cash, (SELECT SUM(FinanceInvoice.Value * FinanceInvoice.Direction) '.
     309        $DbResult = $this->Database->query('SELECT Id, 0 AS Cash, (SELECT SUM(FinanceInvoice.Value * FinanceInvoice.ValueSign) '.
    310310          'FROM FinanceInvoice WHERE (FinanceInvoice.Subject = Subject.Id) AND '.
    311           '(FinanceInvoice.Direction = 1)) as Claims, (SELECT SUM(FinanceInvoice.Value * FinanceInvoice.Direction) '.
     311          '(FinanceInvoice.ValueSign = 1)) as Claims, (SELECT SUM(FinanceInvoice.Value * FinanceInvoice.ValueSign) '.
    312312          'FROM FinanceInvoice WHERE (FinanceInvoice.Subject = Subject.Id) AND ('.
    313           'FinanceInvoice.Direction = 1) AND (TimePayment IS NULL)) as OpenedClaims, '.
    314           '(SELECT SUM(FinanceInvoice.Value * FinanceInvoice.Direction) FROM FinanceInvoice '.
    315           'WHERE (FinanceInvoice.Subject = Subject.Id) AND (FinanceInvoice.Direction = -1)) AS '.
     313          'FinanceInvoice.ValueSign = 1) AND (TimePayment IS NULL)) as OpenedClaims, '.
     314          '(SELECT SUM(FinanceInvoice.Value * FinanceInvoice.ValueSign) FROM FinanceInvoice '.
     315          'WHERE (FinanceInvoice.Subject = Subject.Id) AND (FinanceInvoice.ValueSign = -1)) AS '.
    316316          'Liabilities, (SELECT SUM(FinanceInvoice.Value) FROM FinanceInvoice '.
    317           'WHERE (FinanceInvoice.Subject = Subject.Id) AND (FinanceInvoice.Direction = -1) '.
     317          'WHERE (FinanceInvoice.Subject = Subject.Id) AND (FinanceInvoice.ValueSign = -1) '.
    318318          'AND (TimePayment IS NULL)) AS OpenedLiabilities, '.
    319           '(SELECT SUM(FinanceOperation.Value*FinanceOperation.Direction) '.
    320           'FROM FinanceOperation WHERE FinanceOperation.Subject = Subject.Id AND FinanceOperation.Direction = 1) '.
    321           'AS Gains, (SELECT SUM(FinanceOperation.Value*FinanceOperation.Direction) FROM FinanceOperation WHERE '.
    322           'FinanceOperation.Subject = Subject.Id AND FinanceOperation.Direction = -1) as Spends '.
     319          '(SELECT SUM(FinanceOperation.Value*FinanceOperation.ValueSign) '.
     320          'FROM FinanceOperation WHERE FinanceOperation.Subject = Subject.Id AND FinanceOperation.ValueSign = 1) '.
     321          'AS Gains, (SELECT SUM(FinanceOperation.Value*FinanceOperation.ValueSign) FROM FinanceOperation WHERE '.
     322          'FinanceOperation.Subject = Subject.Id AND FinanceOperation.ValueSign = -1) as Spends '.
    323323          'FROM Subject WHERE Id='.$_GET['Id']);
    324324        $Row = $DbResult->fetch_array();
Note: See TracChangeset for help on using the changeset viewer.