Changeset 486 for trunk


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.
Location:
trunk
Files:
2 added
10 edited

Legend:

Unmodified
Added
Removed

  • trunk/Common/Database.php

    r456 r486  
    150150}
    151151
     152function TimeToMysqlDateTime($Time)
     153{
     154  if($Time == NULL) return(NULL);
     155    else return(date('Y-m-d H:i:s', $Time)); 
     156}
     157
     158function TimeToMysqlDate($Time)
     159{
     160  if($Time == NULL) return(NULL);
     161    else return(date('Y-m-d', $Time)); 
     162}
     163
     164function TimeToMysqlTime($Time)
     165{
     166  if($Time == NULL) return(NULL);
     167    else return(date('H:i:s', $Time)); 
     168}
     169
     170function MysqlDateTimeToTime($DateTime)
     171{
     172  if($DateTime == '') return(0);     
     173  $Parts = explode(' ', $DateTime);
     174  $DateParts = explode('-', $Parts[0]);
     175  $TimeParts = explode(':', $Parts[1]);
     176  $Result = mktime($TimeParts[0], $TimeParts[1], $TimeParts[2], $DateParts[1], $DateParts[2], $DateParts[0]);
     177  return($Result); 
     178}
     179
     180function MysqlDateToTime($Date)
     181{
     182  if($Date == '') return(0);
     183  return(MysqlDateTimeToTime($Date.' 0:0:0')); 
     184}
     185
     186function MysqlTimeToTime($Time)
     187{
     188  if($Time == '') return(0);
     189  return(MysqlDateTimeToTime('0000-00-00 '.$Time)); 
     190}
     191
    152192?>

  • trunk/Common/Global.php

    r485 r486  
    338338}
    339339
    340 function TimeToMysqlDateTime($Time)
    341 {
    342   if($Time == NULL) return(NULL);
    343     else return(date('Y-m-d H:i:s', $Time)); 
    344 }
    345 
    346 function TimeToMysqlDate($Time)
    347 {
    348   if($Time == NULL) return(NULL);
    349     else return(date('Y-m-d', $Time)); 
    350 }
    351 
    352 function MysqlDateTimeToTime($DateTime)
    353 {
    354   if($DateTime == '') return(0);     
    355   $Parts = explode(' ', $DateTime);
    356   $DateParts = explode('-', $Parts[0]);
    357   $TimeParts = explode(':', $Parts[1]);
    358   $Result = mktime($TimeParts[0], $TimeParts[1], $TimeParts[2], $DateParts[1], $DateParts[2], $DateParts[0]);
    359   return($Result); 
    360 }
    361 
    362 function MysqlDateToTime($Date)
    363 {
    364   if($Date == '') return(0);
    365   return(MysqlDateTimeToTime($Date.' 0:0:0')); 
    366 }
    367 
    368 function MysqlTimeToTime($Time)
    369 {
    370   if($Time == '') return(0);
    371   return(MysqlDateTimeToTime('0000-00-00 '.$Time)); 
    372 }
    373 
    374340function HumanDate($Time)
    375341{
    … …  
    416382  return($Result);
    417383}
     384
     385function ExtractTime($Time)
     386{
     387  return(array(
     388        'Year' => date('Y', $Time),
     389        'Month' => date('n', $Time),
     390        'Day' => date('j', $Time),
     391        'Hour' => date('h', $Time),
     392        'Minute' => date('i', $Time),
     393        'Second' => date('s', $Time)
     394  ));
     395}
    418396
    419397function GetQueryStringArray($QueryString)

  • trunk/Modules/Portal/Portal.php

    r484 r486  
    151151      if($_GET['Action'] == 'Login')
    152152      {
     153        if(array_key_exists('Username', $_POST) and array_key_exists('Password', $_POST))
     154        {
    153155        $Form = new Form('UserLogin');
    154156        $Form->OnSubmit = '?Action=Login';
    … …  
    163165            '<a href="?Action=PasswordRecovery">Obnova zapomenutého hesla</a></div>';
    164166        }
     167        } else $Output .= $this->SystemMessage('Přihlášení', 'Nezadány přihlašovací údaje');
    165168      } else
    166169      if($_GET['Action'] == 'Logout')

  • trunk/finance/bills.php

    r477 r486  
    212212  }
    213213
    214   function CustomGenerate()
    215   {
    216     global $Database;
    217    
    218     $DbResult = $this->Database->select('finance_operations', '*', '(date="2008-02-01" AND comment="Poplatek za měsíc Únor") OR '.
    219     '(date="2008-01-01" AND comment="Poplatek za měsíc Leden") OR (date="2007-12-01" AND comment="Poplatek za měsíc Prosinec") AND (bill_id = 0)');
    220     while($Row = $DbResult->fetch_array())
    221     {
    222       echo($Row['id']."<br>\n");
    223       $Time = MysqlDateToTime($Row['date']);
    224       $BillId = $this->CreateBill($Row['user'], array(array('Description' => 'Poplatek za připojení k síti', 'Price' => (-$Row['money']), 'Quantity' => 1)), $Time, $Time + (date("t", time()) - 1) * 24 * 3600); 
    225      $this->Database->update('finance_operations', 'id='.$Row['id'], array('bill_id' => $BillId));
    226     }
    227   }
    228 
    229214  function ShowPage()
    230215  {

  • trunk/finance/finance.php

    r481 r486  
    177177    while($Member = $DbResult->fetch_assoc())
    178178    {
    179       $DbResult2 = $this->Database->query('SELECT ((SELECT COALESCE(SUM(Value), 0) FROM FinanceOperation WHERE Subject='.$Member['Subject'].') + (SELECT COALESCE(SUM(-Value), 0) FROM FinanceClaimsLiabilities WHERE Subject='.$Member['Subject'].')) as Cash');
     179      $DbResult2 = $this->Database->query('SELECT ((SELECT COALESCE(SUM(Value), 0) FROM FinanceOperation '.
     180      'WHERE Subject='.$Member['Subject'].') + (SELECT COALESCE(SUM(-Value), 0) FROM FinanceInvoice '.
     181      'WHERE Subject='.$Member['Subject'].')) as Cash');
    180182      $Cash = $DbResult2->fetch_row();
    181183      $Cash = $Cash[0];

  • trunk/finance/manage.php

    r482 r486  
    133133      $DbRow = $DbResult->fetch_array();
    134134      $Advances = $DbRow[0];
    135       $DbResult = $this->Database->select('FinanceClaimsLiabilities', '*', 'Subject='.$Subject.' AND TimePayment IS NULL AND Value > 0 ORDER BY TimeCreation LIMIT 1');
     135      $DbResult = $this->Database->select('FinanceInvoice', '*', 'Subject='.$Subject.
     136      ' AND TimePayment IS NULL AND Value > 0 ORDER BY TimeCreation LIMIT 1');
    136137      //echo($Database->error);
    137138      if($DbResult->num_rows > 0)
    … …  
    140141        if($Advances > $OpenedClaim['Value'])
    141142        {
    142           $this->Database->update('FinanceClaimsLiabilities', 'Id='.$OpenedClaim['Id'], array('TimePayment' => TimeToMysqlDateTime($LastInsertTime)));
    143           $this->Database->insert('FinanceAdvances', array('Subject' => $OpenedClaim['Subject'], 'Value' => -$OpenedClaim['Value'], 'TimeCreation' => TimeToMysqlDateTime($LastInsertTime), 'CashFlowId' => $OpenedClaim['Id'], 'Direction' => 'In'));
     143          $this->Database->update('FinanceInvoice', 'Id='.$OpenedClaim['Id'],
     144            array('TimePayment' => TimeToMysqlDateTime($LastInsertTime)));
     145          $this->Database->insert('FinanceAdvances', array('Subject' =>
     146            $OpenedClaim['Subject'], 'Value' => -$OpenedClaim['Value'],
     147            'TimeCreation' => TimeToMysqlDateTime($LastInsertTime),
     148            'CashFlowId' => $OpenedClaim['Id'], 'Direction' => 'In'));
    144149          //echo($Database->LastQuery);
    145150        } else break;
    … …  
    154159
    155160    $BillCode = $this->System->Modules['Finance']->GetNextDocumentLineNumber($DocumentLine);
    156     $this->Database->insert('FinanceClaimsLiabilities', array('Text' => $Text,
     161    $this->Database->insert('FinanceInvoice', array(
    157162      'Subject' => $Subject, 'TimeCreation' => TimeToMysqlDateTime($TimeCreation),
    158163      'TimeDue' => TimeToMysqlDateTime($TimeDue), 'Value' => $Value, 'BillCode' => $BillCode,
    159164      'PeriodFrom' => TimeToMysqlDate($PeriodFrom), 'PeriodTo' => TimeToMysqlDate($PeriodTo)));
     165    $InvoiceId = $this->Database->insert_id;
     166    $this->Database->insert('FinanceInvoice', array('FinanceInvoice' => $InvoiceId,
     167      'Description' => $Text, 'Price' => $Value, 'Quantity' => 1, 'VAT' => 0));
    160168    $Output = '.'; //$this->Database->LastQuery.'<br />';
    161169    //$LastInsertTime = $Time;
    … …  
    253261      $Form->Values['DocumentLine'], NULL, NULL);
    254262    $LastId = $this->Database->insert_id;
    255     $DbResult = $this->Database->select('FinanceClaimsLiabilities', 'BillCode', 'Id='.$LastId);
     263    $DbResult = $this->Database->select('FinanceInvoice', 'BillCode', 'Id='.$LastId);
    256264    $DbRow = $DbResult->fetch_assoc();
    257265    $Output = $this->SystemMessage('Finance', 'Faktura vložena '.$DbRow['BillCode'].'.');
    … …  
    360368    $Output .= $Finance->RecalculateSegmentParameters();
    361369    $Output .= $Finance->LoadTariffs();
    362     //$InvoiceGenerator->CustomGenerate();
    363370    $Output .= $Finance->RecalculateMemberPayment();
    364371
    … …  
    446453          $BillId = $this->System->Modules['Bill']->CreateBill($Member['Subject'],
    447454            $BillItems, time(), time() + 3600 * 24 * INVOICE_DUE_DAYS, $BillCode, $Period['From'], $Period['To']);
    448           $this->Database->insert('FinanceClaimsLiabilities', array('Value' => $PayPerPeriod,
     455          $this->Database->insert('FinanceInvoice', array(
    449456            'Subject' => $Member['Subject'], 'TimeCreation' => 'NOW()',
    450             'TimeDue' => 'DATE_ADD(NOW(), INTERVAL '.INVOICE_DUE_DAYS.' DAY)',
    451             'Text' => 'Vyúčtování služeb za období '.$TimePeriodText,
     457            'TimeDue' => 'DATE_ADD(NOW(), INTERVAL '.INVOICE_DUE_DAYS.' DAY)',             
    452458            'Bill' => $BillId, 'BillCode' => $BillCode));
     459          $InvoiceId = $this->Database->insert_id;
     460          $this->Database->insert('FinanceInvoiceItem', array('FinanceInvoice' => $InvoiceId,
     461            'Value' => $PayPerPeriod, 'Text' => 'Vyúčtování služeb za období '.$TimePeriodText,
     462           'Quantity' => 1, 'VAT' => 0));
     463         
    453464          $Output .= $this->SendPaymentEmail($Member['Id']);
    454465          $this->Database->update('Member', 'Id='.$Member['Id'], array('BillingPeriodLastDate' => TimeToMysqlDateTime($Period['To'])));
    … …  
    531542      //$Member['BillingPeriodLastDate'] = '2009-04-01';
    532543      $DbResult = $this->Database->query('SELECT T1.* FROM ((SELECT Text, Time, Value AS Value, Bill FROM FinanceOperation WHERE (Subject='.$Member['Subject'].')) UNION ALL '.
    533       '(SELECT Text, TimeCreation as Time, -Value as Value, Bill FROM FinanceClaimsLiabilities WHERE (Subject='.$Member['Subject'].')) ORDER BY Time DESC) AS T1 WHERE (T1.Time > "'.$Member['BillingPeriodLastDate'].'")');
     544      '(SELECT Text, TimeCreation as Time, -Value as Value, Bill FROM FinanceInvoice WHERE (Subject='.$Member['Subject'].')) ORDER BY Time DESC) AS T1 WHERE (T1.Time > "'.$Member['BillingPeriodLastDate'].'")');
    534545      while($DbRow = $DbResult->fetch_assoc())
    535546      {
    … …  
    550561  function GenerateBills()
    551562  {
    552     // FinanceClaimsLiablities
    553     $DbResult = $this->Database->query('SELECT * FROM FinanceClaimsLiabilities WHERE (BillCode <> "") AND (Value != 0) AND (Bill = 0)');
     563    // FinanceInvoice
     564    $DbResult = $this->Database->query('SELECT * FROM FinanceInvoice WHERE (BillCode <> "") AND (Value != 0) AND (Bill = 0)');
    554565    while($Row = $DbResult->fetch_assoc())
    555566    {
    … …  
    558569        MysqlDateTimeToTime($Row['TimeCreation']), MysqlDateTimeToTime($Row['TimeDue']),
    559570        $Row['BillCode'], 'invoice', MysqlDateToTime($Row['PeriodFrom']), MysqlDateToTime($Row['PeriodTo']));
    560       $this->Database->update('FinanceClaimsLiabilities', 'Id='.$Row['Id'], array('Bill' => $BillId));
     571      $this->Database->update('FinanceInvoice', 'Id='.$Row['Id'], array('Bill' => $BillId));
    561572      echo('.');
    562573    }

  • trunk/finance/user_state.php

    r479 r486  
    5252      else $SubjectParam = '';
    5353    $Output .= 'Roky: ';
    54     $DbResult = $this->Database->query('SELECT * FROM FinanceYear');
     54    $DbResult = $this->Database->query('SELECT * FROM FinanceYear WHERE Year >= '.
     55      substr($Member['MembershipDate'], 0, strpos($Member['MembershipDate'], '-')));
    5556    while($FinanceYear = $DbResult->fetch_assoc())
    5657    {
    … …  
    6263   
    6364    // Account state   
    64     $UserOperationTableQuery = '((SELECT Text, Time, Value AS Value, Bill, BillCode FROM FinanceOperation WHERE (Subject='.$Subject['Id'].')) UNION ALL '.
    65       '(SELECT Text, TimeCreation as Time, -Value as Value, Bill, BillCode FROM FinanceClaimsLiabilities WHERE (Subject='.$Subject['Id'].')) ORDER BY Time DESC) AS T1';
     65    $UserOperationTableQuery = '((SELECT Text, Time, Value AS Value, Bill, BillCode, NULL AS PeriodFrom, NULL AS PeriodTo '.
     66      'FROM FinanceOperation WHERE (Subject='.$Subject['Id'].')) UNION ALL '.
     67      '(SELECT (SELECT GROUP_CONCAT(Description SEPARATOR ",") FROM FinanceInvoiceItem WHERE FinanceInvoice=FinanceInvoice.Id) AS Text, '.
     68      'TimeCreation as Time, -Value as Value, Bill, BillCode, PeriodFrom, PeriodTo FROM FinanceInvoice WHERE (Subject='.$Subject['Id'].')) ORDER BY Time DESC) AS T1';
    6669    $DbResult = $this->Database->query('SELECT SUM(T1.Value) AS Total FROM '.$UserOperationTableQuery);
    6770    $DbRow = $DbResult->fetch_array();
    … …  
    7275    // Tabulka operaci
    7376    $Output .= 'Výpis operací pro subjekt '.$Subject['Name'].':<br />';
    74     $Output .= '<table style="font-size: smaller;" class="WideTable"><tr><th>Datum</th><th>Popis</th><th>Částka</th><th>Doklad</th></tr>';
     77    $Output .= '<table style="font-size: smaller;" class="WideTable"><tr>'.
     78    '<th>Datum</th><th>Popis</th><th>Částka</th><th>Období</th><th>Doklad</th></tr>';
    7579    $Operations = array();
    7680    $DbResult = $this->Database->query('SELECT T1.* FROM '.$UserOperationTableQuery.$YearWhere);
    … …  
    8387      if($Row['Value'] > 0) $Row['Value'] = '+'.$Row['Value'];
    8488      if($Row['BillCode'] == '') $Row['BillCode'] = 'PDF';
    85       if($Row['Bill'] > 0) $Invoice = '<a href="bill.php?Id='.$Row['Bill'].'">'.$Row['BillCode'].'</a>'; else $Invoice = '&nbsp;';
    86       $Output .= '<tr><td style="text-align: right;">'.HumanDate($Row['Time']).'</td><td style="text-align: left;">'.$Row['Text'].'</td><td style="text-align: right;">'.$Row['Value'].'</td><td style="text-align: center;">'.$Invoice.'</td></tr>';
     89      if($Row['Bill'] > 0) $Invoice = '<a href="bill.php?Id='.$Row['Bill'].'">'.$Row['BillCode'].'</a>';
     90        else $Invoice = '&nbsp;';
     91      if($Row['PeriodFrom'] != '') $Period = HumanDate($Row['PeriodFrom']).' - '.HumanDate($Row['PeriodTo']);
     92        else $Period = '&nbsp;';
     93      $Output .= '<tr><td style="text-align: right;">'.HumanDate($Row['Time']).'</td>'.
     94      '<td style="text-align: left;">'.$Row['Text'].'</td>'.
     95      '<td style="text-align: right;">'.$Row['Value'].'</td>'.
     96      '<td style="text-align: center;">'.$Period.'</td>'.
     97      '<td style="text-align: center;">'.$Invoice.'</td></tr>';
    8798      $SumValue = $SumValue + $Row['Value'];
    8899    }
    89     $Output .= '<tr><td style="text-align: right;" colspan="2"><strong>Celkem za rok</strong></td><td style="text-align: right;"><strong>'.$SumValue.'</strong></td><td style="text-align: center;">&nbsp;</td></tr>';
     100    $Output .= '<tr><td style="text-align: right;" colspan="2"><strong>Celkem za rok</strong></td>'.
     101    '<td style="text-align: right;"><strong>'.$SumValue.'</strong></td>'.
     102    '<td style="text-align: center;">&nbsp;</td>'.
     103    '<td style="text-align: center;">&nbsp;</td></tr>';
    90104   
    91105   

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

  • trunk/form_classes.php

    r484 r486  
    1616
    1717$FormClasses = array(   
     18  'File' => array(
     19    'Title' => 'Soubor',
     20    'Table' => 'File',
     21    'Items' => array(
     22      'Name' => array('Type' => 'String', 'Caption' => 'Jméno', 'Default' => ''),
     23      'Directory' => array('Type' => 'TDirectory', 'Caption' => 'Adresář', 'Default' => '', 'Null' => true),
     24      'Size' => array('Type' => 'Integer', 'Caption' => 'Velikost', 'Default' => ''),
     25    ),
     26  ),
     27  'FileDirectory' => array(
     28    'Title' => 'Adresář souborů',
     29    'Table' => 'FileDirectory',
     30    'Items' => array(
     31      'Name' => array('Type' => 'String', 'Caption' => 'Jméno', 'Default' => ''),
     32      'Parent' => array('Type' => 'TDirectory', 'Caption' => 'Nadřazený adresář', 'Default' => '', 'Null' => true),
     33    ),
     34  ),
    1835  'NetworkLink' => array(
    1936    'Title' => 'Síťové propojení',
    … …  
    331348    'DefaultSortColumn' => 'Comment',
    332349    'Items' => array(
     350      'Subject' => array('Type' => 'TSubject', 'Caption' => 'Vlastník', 'Default' => ''),
    333351      'Comment' => array('Type' => 'String', 'Caption' => 'Komentář', 'Default' => ''),
    334352      'Number' => array('Type' => 'String', 'Caption' => 'Číslo', 'Default' => ''),
    335353      'TimeCreate' => array('Type' => 'Date', 'Caption' => 'Čas vytvoření', 'Default' => ''),
     354      'TimeEnd' => array('Type' => 'Date', 'Caption' => 'Čas zrušení', 'Default' => ''),
     355      'Operations' => array('Type' => 'TFinanceOperationListAccount', 'Caption' => 'Operace', 'Default' => ''),
    336356    ),
    337357  ),
    … …  
    500520    ),
    501521  ),
    502   'FinanceInvoiceItem' => array(
    503     'Title' => 'Položka faktury',
    504     'Items' => array(
    505       'Value' => array('Type' => 'Float', 'Caption' => 'Částka [Kč]', 'Default' => '0', 'Suffix' => 'Kč'),
    506       'Text' => array('Type' => 'String', 'Caption' => 'Popis', 'Default' => 'Položka'),
    507       'Quantity' => array('Type' => 'Integer', 'Caption' => 'Množství', 'Default' => '1'),
    508       'Tax' => array('Type' => 'Integer', 'Caption' => 'Daň [%]', 'Default' => '19', 'Suffix' => '%'),
    509     ),
    510   ),
    511522  'FinanceOperation' => array(
    512523    'Title' => 'Finanční operace',
    … …  
    526537    ),
    527538  ),
    528   'FinanceClaimsLiabilities' => array(
     539  'FinanceInvoice' => array(
    529540    'Title' => 'Závazky a pohledávky',
    530     'Table' => 'FinanceClaimsLiabilities',
     541    'Table' => 'FinanceInvoice',
    531542    'DefaultSortColumn' => 'TimeCreation',
    532543    'Items' => array(
     544      'BillCode' => array('Type' => 'String', 'Caption' => 'Označení', 'Default' => ''),
     545      'Subject' => array('Type' => 'TSubject', 'Caption' => 'Subjekt', 'Default' => ''),
    533546      'TimeCreation' => array('Type' => 'Date', 'Caption' => 'Čas vytvoření', 'Default' => ''),
    534547      'TimeDue' => array('Type' => 'Date', 'Caption' => 'Čas splatnosti', 'Default' => ''),
    535548      'TimePayment' => array('Type' => 'Date', 'Caption' => 'Čas zaplacení', 'Default' => ''),
    536       'Subject' => array('Type' => 'TSubject', 'Caption' => 'Subjekt', 'Default' => ''),
    537       'BillCode' => array('Type' => 'String', 'Caption' => 'Označení', 'Default' => ''),
    538549      'Value' => array('Type' => 'Integer', 'Caption' => 'Částka', 'Default' => '0', 'Suffix' => 'Kč'),
    539       'Text' => array('Type' => 'String', 'Caption' => 'Popis', 'Default' => ''),
    540       'Bill' => array('Type' => 'TFinanceBill', 'Caption' => 'Doklad', 'Default' => '', 'Null' => true),
     550      'File' => array('Type' => 'TFile', 'Caption' => 'Doklad', 'Default' => '', 'Null' => true),
     551      'Items' => array('Type' => 'TFinanceInvoiceItemListInvoice', 'Caption' => 'Položky', 'Default' => ''),
     552    ),
     553  ),
     554  'FinanceInvoiceItem' => array(
     555    'Title' => 'Položka faktury',
     556    'Table' => 'FinanceInvoiceItem',
     557    'Items' => array(
     558      'FinanceInvoice' => array('Type' => 'TFinanceInvoice', 'Caption' => 'Faktura', 'Default' => '0'),
     559      'Description' => array('Type' => 'String', 'Caption' => 'Popis', 'Default' => 'Položka'),
     560      'Price' => array('Type' => 'Float', 'Caption' => 'Částka [Kč]', 'Default' => '0', 'Suffix' => 'Kč'),
     561      'Quantity' => array('Type' => 'Integer', 'Caption' => 'Množství', 'Default' => '1'),
     562      'VAT' => array('Type' => 'Integer', 'Caption' => 'Daň [%]', 'Default' => '19', 'Suffix' => '%'),
    541563    ),
    542564  ),
    … …  
    835857    'Filter' => '1',
    836858  ),
    837   'TFinanceBill' => array(
    838     'Type' => 'Reference',
    839     'Table' => 'FinanceBills',
     859  'TFile' => array(
     860    'Type' => 'Reference',
     861    'Table' => 'File',
    840862    'Id' => 'Id',
    841863    'Name' => 'Id',
    … …  
    894916    'Type' => 'Reference',
    895917    'Table' => 'Language',
     918    'Id' => 'Id',
     919    'Name' => 'Name',
     920    'Filter' => '1',
     921  ),
     922  'TDirectory' => array(
     923    'Type' => 'Reference',
     924    'Table' => 'FileDirectory',
    896925    'Id' => 'Id',
    897926    'Name' => 'Name',
    … …  
    940969    'Filter' => '1',
    941970  ),
     971  'TFinanceInvoice' => array(
     972    'Type' => 'Reference',
     973    'Table' => 'FinanceInvoice',
     974    'Id' => 'Id',
     975    'Name' => 'BillCode',
     976    'Filter' => '1',
     977  ),
    942978  'TPermissionGroup' => array(
    943979    'Type' => 'Reference',
    … …  
    10501086    'Id' => 'Id',
    10511087    'Ref' => 'Interface2',
     1088    'Filter' => '1',
     1089  ),
     1090  'TFinanceInvoiceItemListInvoice' => array(
     1091    'Type' => 'ManyToOne',
     1092    'Table' => 'FinanceInvoiceItem',
     1093    'Id' => 'Id',
     1094    'Ref' => 'FinanceInvoice',
     1095    'Filter' => '1',
     1096  ),
     1097  'TFinanceOperationListAccount' => array(
     1098    'Type' => 'ManyToOne',
     1099    'Table' => 'FinanceOperation',
     1100    'Id' => 'Id',
     1101    'Ref' => 'BankAccount',
    10521102    'Filter' => '1',
    10531103  ),

  • trunk/sql/updates/479.sql

    r482 r486  
    4141  ADD CONSTRAINT `Service_ibfk_1` FOREIGN KEY (`Category`) REFERENCES `ServiceCategory` (`Id`);
    4242
     43ALTER TABLE `ServiceCustomerRel` ADD FOREIGN KEY ( `Service` ) REFERENCES `Service` (
     44`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;
     45
    4346-- Transform data from FinanceTariff
    4447INSERT INTO Service (SELECT Id AS Id, Name, `Group` AS Category, Price, MemberCount AS CustomerCount,
    … …  
    6063ALTER TABLE `Service` ADD INDEX ( `ReplaceId` ) ;
    6164ALTER TABLE `Service` ADD FOREIGN KEY ( `ReplaceId` ) REFERENCES `Service` (`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;
     65
Note: See TracChangeset for help on using the changeset viewer.