Ignore:
Timestamp:
Feb 10, 2013, 9:20:06 PM (12 years ago)
Author:
chronos
Message:
  • Upraveno: Příprava na zrušení tabulek dokladů a jejich převedení na tabulky File a FinanceInvoice.
File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/finance/zivnost.php

    r479 r486  
    1111  var $StartEvidence = 0;
    1212
    13   /*function TransformFinance()
    14   {
    15     //$Database->Query('SELECT user as Subject, date as TimeCreation, date as TimeDue, date as TimePayment, -money as Value, bill_id as Bill, comment as Text FROM `finance_operations` WHERE Comment LIKE "Poplatek za měsíc%" AND date >= "2007-12-01"');
    16     //'TRUNCATE TABLE FinanceCashFlow; INSERT INTO FinanceCashFlow (SELECT '' as Id, user as Subject, comment as Text, date as Time, 1 as Taxable, 0 as Cash, money as Value, bill_id as Bill FROM `finance_operations` WHERE comment NOT LIKE "Poplatek za měsíc%" AND date >= "2007-12-01")'
    17     // 'UPDATE finance_operations SET date="2007-11-24" WHERE comment="Jednorázový poplatek za společné zařízení"'
    18 
    19     $DbResult = $this->Database->query('SELECT * FROM users WHERE role=2');
    20     while($User = $DbResult->fetch_array())
    21     {
    22       $DbResult2 = $this->Database->query('SELECT SUM(money) FROM finance_operations WHERE user='.$User['id'].' AND date < "2007-12-01"');
    23       $Row = $DbResult2->fetch_array();
    24       echo($User['second_name'].' '.$User['first_name'].' '.$Row[0].'<br />');
    25       //    if($Row[0] > 0) $Database->insert('FinanceCashFlow', array('Text' => 'Přijatá záloha (z období před účetnictvím)', 'Subject' => $User['id'], 'Time' => '2007-01-01', 'Taxable' => 1, 'Cash' => 0, 'Value' => $Row[0], 'Bill' => 0));
    26       if($Row[0] < 0)
    27       {
    28         $this->Database->insert('FinanceClaimsLiabilities', array('Text' => 'Připojení k síti (z období před účetnictvím)', 'Subject' => $User['id'], 'TimeCreation' => '2007-12-01', 'TimeDue' => '2007-12-14', 'TimePayment' => '', 'Value' => (-$Row[0]), 'Bill' => 0));
    29         //echo($Database->LastQuery.'<br />');
    30       }
    31     }
    32   }*/
    3313  function GetTimePeriodBalance($StartTime, $EndTime)
    3414  {
    … …  
    5030    $Balance['Spend']['End'] = $Row[0] + 0;
    5131
    52     $DbResult = $this->Database->query('SELECT SUM(Value) FROM FinanceClaimsLiabilities WHERE (TimeCreation < "'.TimeToMysqlDateTime($StartTime).'") AND (TimeCreation >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Value > 0)');
     32    $DbResult = $this->Database->query('SELECT SUM(Value) FROM FinanceInvoice WHERE (TimeCreation < "'.TimeToMysqlDateTime($StartTime).'") AND (TimeCreation >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Value > 0)');
    5333    $Row = $DbResult->fetch_array();
    5434    $Balance['Claims']['Start'] = $Row[0] + 0;
    55     $DbResult = $this->Database->query('SELECT SUM(Value) FROM FinanceClaimsLiabilities WHERE (TimeCreation <= "'.TimeToMysqlDateTime($EndTime).'") AND (TimeCreation >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Value > 0)');
     35    $DbResult = $this->Database->query('SELECT SUM(Value) FROM FinanceInvoice WHERE (TimeCreation <= "'.TimeToMysqlDateTime($EndTime).'") AND (TimeCreation >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Value > 0)');
    5636    $Row = $DbResult->fetch_array();
    5737    $Balance['Claims']['End'] = $Row[0] + 0;
    5838
    59     $DbResult = $this->Database->query('SELECT SUM(-Value) FROM FinanceClaimsLiabilities WHERE (TimeCreation < "'.TimeToMysqlDateTime($StartTime).'") AND (TimeCreation >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Value < 0)');
     39    $DbResult = $this->Database->query('SELECT SUM(-Value) FROM FinanceInvoice WHERE (TimeCreation < "'.TimeToMysqlDateTime($StartTime).'") AND (TimeCreation >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Value < 0)');
    6040    $Row = $DbResult->fetch_array();
    6141    $Balance['Liabilities']['Start'] = $Row[0] + 0;
    62     $DbResult = $this->Database->query('SELECT SUM(-Value) FROM FinanceClaimsLiabilities WHERE (TimeCreation <= "'.TimeToMysqlDateTime($EndTime).'") AND (TimeCreation >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Value < 0)');
     42    $DbResult = $this->Database->query('SELECT SUM(-Value) FROM FinanceInvoice WHERE (TimeCreation <= "'.TimeToMysqlDateTime($EndTime).'") AND (TimeCreation >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Value < 0)');
    6343    $Row = $DbResult->fetch_array();
    6444    $Balance['Liabilities']['End'] = $Row[0] + 0;
    … …  
    227207        $Output .= '<table style="font-size: smaller;" class="WideTable">';
    228208        $Output .= '<tr><th>Čas vystavení</th><th>Kód</th><th>Subjekt</th><th>Text</th><th>Hodnota [Kč]</th></tr>';
    229         $DbResult = $this->Database->query('SELECT * FROM FinanceClaimsLiabilities JOIN Subject ON Subject.Id = FinanceClaimsLiabilities.Subject '.
    230           'WHERE (Value > 0) AND (FinanceClaimsLiabilities.TimeCreation >= "'.$Year['DateStart'].'") AND (FinanceClaimsLiabilities.TimeCreation <= "'.$Year['DateEnd'].'") ORDER BY TimeCreation');
     209        $DbResult = $this->Database->query('SELECT *, (SELECT GROUP_CONCAT(Description SEPARATOR ",") FROM FinanceInvoiceItem WHERE FinanceInvoiceItem.FinanceInvoice = FinanceInvoice.Id) AS Text '.
     210          'FROM FinanceInvoice JOIN Subject ON Subject.Id = FinanceInvoice.Subject '.
     211          'WHERE (Value > 0) AND (FinanceInvoice.TimeCreation >= "'.$Year['DateStart'].
     212          '") AND (FinanceInvoice.TimeCreation <= "'.$Year['DateEnd'].'") ORDER BY TimeCreation');
    231213        while($Row = $DbResult->fetch_array())
    232214        {
    … …  
    247229        $Output .= '<table style="font-size: smaller;" class="WideTable">';
    248230        $Output .= '<tr><th>Čas vystavení</th><th>Kód</th><th>Subjekt</th><th>Text</th><th>Hodnota [Kč]</th></tr>';
    249         $DbResult = $this->Database->query('SELECT * FROM FinanceClaimsLiabilities JOIN Subject ON Subject.Id = FinanceClaimsLiabilities.Subject '.
    250           'WHERE (Value < 0) AND (FinanceClaimsLiabilities.TimeCreation >= "'.$Year['DateStart'].'") AND (FinanceClaimsLiabilities.TimeCreation <= "'.$Year['DateEnd'].'") ORDER BY TimeCreation');
     231        $DbResult = $this->Database->query('SELECT *, (SELECT GROUP_CONCAT(Description SEPARATOR ",") FROM FinanceInvoiceItem WHERE FinanceInvoiceItem.FinanceInvoice = FinanceInvoice.Id) AS Text '.
     232          'FROM FinanceInvoice JOIN Subject ON Subject.Id = FinanceInvoice.Subject '.
     233          'WHERE (Value < 0) AND (FinanceInvoice.TimeCreation >= "'.$Year['DateStart'].
     234          '") AND (FinanceInvoice.TimeCreation <= "'.$Year['DateEnd'].'") ORDER BY TimeCreation');
    251235        while($Row = $DbResult->fetch_array())
    252236        {
    … …  
    264248        $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>';
    265249        $DbResult = $this->Database->query('SELECT Id, Name, 0 AS Cash, '.
    266         '(SELECT SUM(T1.Value) FROM FinanceClaimsLiabilities AS T1 WHERE (T1.Subject = Subject.Id) AND (T1.Value > 0)) as Claims, '.
    267         '(SELECT SUM(T2.Value) FROM FinanceClaimsLiabilities AS T2 WHERE (T2.Subject = Subject.Id) AND (T2.Value > 0) AND (TimePayment IS NULL)) as OpenedClaims, '.
    268         '(SELECT -SUM(T3.Value) FROM FinanceClaimsLiabilities AS T3 WHERE (T3.Subject = Subject.Id) AND (T3.Value  < 0)) as Liabilities, '.
    269         '(SELECT -SUM(T4.Value) FROM FinanceClaimsLiabilities AS T4 WHERE (T4.Subject = Subject.Id) AND (T4.Value < 0) AND (TimePayment IS NULL)) AS OpenedLiabilities, '.
     250        '(SELECT SUM(T1.Value) FROM FinanceInvoice AS T1 WHERE (T1.Subject = Subject.Id) AND (T1.Value > 0)) as Claims, '.
     251        '(SELECT SUM(T2.Value) FROM FinanceInvoice AS T2 WHERE (T2.Subject = Subject.Id) AND (T2.Value > 0) AND (TimePayment IS NULL)) as OpenedClaims, '.
     252        '(SELECT -SUM(T3.Value) FROM FinanceInvoice AS T3 WHERE (T3.Subject = Subject.Id) AND (T3.Value  < 0)) as Liabilities, '.
     253        '(SELECT -SUM(T4.Value) FROM FinanceInvoice AS T4 WHERE (T4.Subject = Subject.Id) AND (T4.Value < 0) AND (TimePayment IS NULL)) AS OpenedLiabilities, '.
    270254        '(SELECT SUM(T5.Value) FROM FinanceOperation AS T5 WHERE (T5.Subject = Subject.Id) AND (T5.Value > 0)) AS Gains, '.
    271255        '(SELECT -SUM(T6.Value) FROM FinanceOperation AS T6 WHERE (T6.Subject = Subject.Id) AND (T6.Value < 0)) AS Spends '.
    … …  
    306290        $Output .= '<table style="font-size: smaller;" border="1" cellspacing="0" cellpadding="3">';
    307291        $Output .= '<tr><th>Datum vytvoření</th><th>Datum zaplacení</th><th>Název</th><th>Hodnota [Kč]</th><th>Doklad</th></tr>';
    308         $DbResult = $this->Database->select('FinanceClaimsLiabilities', '*', 'Subject='.$_GET['Id'].' ORDER BY TimeCreation');
     292        $DbResult = $this->Database->select('FinanceInvoice', '*, (SELECT GROUP_CONCAT(Description SEPARATOR ",") FROM FinanceInvoiceItem WHERE FinanceInvoiceItem.FinanceInvoice = FinanceInvoice.Id) AS Text', 'Subject='.$_GET['Id'].' ORDER BY TimeCreation');
    309293        while($Row = $DbResult->fetch_array())
    310294        {
    … …  
    324308        $Output .= '</table>';*/
    325309
    326         $DbResult = $this->Database->query('SELECT Id, 0 AS Cash, (SELECT SUM(FinanceClaimsLiabilities.Value) '.
    327           'FROM FinanceClaimsLiabilities WHERE FinanceClaimsLiabilities.Subject = Subject.Id AND '.
    328           'FinanceClaimsLiabilities.Value > 0) as Claims, (SELECT SUM(FinanceClaimsLiabilities.Value) '.
    329           'FROM FinanceClaimsLiabilities WHERE FinanceClaimsLiabilities.Subject = Subject.Id AND '.
    330           'FinanceClaimsLiabilities.Value > 0 AND TimePayment IS NULL) as OpenedClaims, '.
    331           '(SELECT SUM(FinanceClaimsLiabilities.Value) FROM FinanceClaimsLiabilities '.
    332           'WHERE FinanceClaimsLiabilities.Subject = Subject.Id AND FinanceClaimsLiabilities.Value < 0) AS '.
    333           'Liabilities, (SELECT SUM(FinanceClaimsLiabilities.Value) FROM FinanceClaimsLiabilities '.
    334           'WHERE FinanceClaimsLiabilities.Subject = Subject.Id AND FinanceClaimsLiabilities.Value < 0 '.
     310        $DbResult = $this->Database->query('SELECT Id, 0 AS Cash, (SELECT SUM(FinanceInvoice.Value) '.
     311          'FROM FinanceInvoice WHERE FinanceInvoice.Subject = Subject.Id AND '.
     312          'FinanceInvoice.Value > 0) as Claims, (SELECT SUM(FinanceInvoice.Value) '.
     313          'FROM FinanceInvoice WHERE FinanceInvoice.Subject = Subject.Id AND '.
     314          'FinanceInvoice.Value > 0 AND TimePayment IS NULL) as OpenedClaims, '.
     315          '(SELECT SUM(FinanceInvoice.Value) FROM FinanceInvoice '.
     316          'WHERE FinanceInvoice.Subject = Subject.Id AND FinanceInvoice.Value < 0) AS '.
     317          'Liabilities, (SELECT SUM(FinanceInvoice.Value) FROM FinanceInvoice '.
     318          'WHERE FinanceInvoice.Subject = Subject.Id AND FinanceInvoice.Value < 0 '.
    335319          'AND TimePayment IS NULL) as OpenedLiabilities, (SELECT SUM(FinanceOperation.Value) '.
    336320          'FROM FinanceOperation WHERE FinanceOperation.Subject = Subject.Id AND FinanceOperation.Value > 0) '.
Note: See TracChangeset for help on using the changeset viewer.