Changeset 720

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

Files:

• Application/FormClasses.php (modified) (1 diff)
• Application/Version.php (modified) (1 diff)
• Common/Setup/Updates.php (modified) (3 diffs)
• Modules/Finance/Bill.php (modified) (2 diffs)
• Modules/Finance/Finance.php (modified) (5 diffs)
• Modules/Finance/Manage.php (modified) (9 diffs)
• Modules/Finance/UserState.php (modified) (2 diffs)
• Modules/Finance/Zivnost.php (modified) (7 diffs)
• Modules/Subject/Subject.php (modified) (1 diff)

trunk/Application/FormClasses.php

@@ -96 +96 @@
         'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperation`.`Taxable` = 1))'),
       'Claims' => array('Type' => 'Integer', 'Caption' => 'Pohledávky', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
-        'SQL' => '(SELECT ROUND(SUM(`FinanceInvoice`.`Value`)) FROM `FinanceInvoice` WHERE (`FinanceInvoice`.`Time` <= `DateEnd`) '.
-        'AND (`FinanceInvoice`.`Time` >= `DateStart`) AND (`FinanceInvoice`.`Value` > 0))'),
+        'SQL' => '(SELECT ROUND(SUM(`FinanceInvoice`.`Value` * `FinanceInvoice`.`Direction`)) FROM `FinanceInvoice` WHERE (`FinanceInvoice`.`Time` <= `DateEnd`) '.
+        'AND (`FinanceInvoice`.`Time` >= `DateStart`) AND (`FinanceInvoice`.`Direction` = 1))'),
       'Liabilities' => array('Type' => 'Integer', 'Caption' => 'Závazky', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
-        'SQL' => '(SELECT -ROUND(SUM(`FinanceInvoice`.`Value`)) FROM `FinanceInvoice` WHERE (`FinanceInvoice`.`Time` <= `DateEnd`) '.
-        'AND (`FinanceInvoice`.`Time` >= `DateStart`) AND (`FinanceInvoice`.`Value` < 0))'),
+        'SQL' => '(SELECT -ROUND(SUM(`FinanceInvoice`.`Value` * `FinanceInvoice`.`Direction`)) FROM `FinanceInvoice` WHERE (`FinanceInvoice`.`Time` <= `DateEnd`) '.
+        'AND (`FinanceInvoice`.`Time` >= `DateStart`) AND (`FinanceInvoice`.`Direction` = -1))'),
       'InvoiceBalance' => array('Type' => 'Integer', 'Caption' => 'Fakturační rozdíl', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
-        'SQL' => '(SELECT ROUND(SUM(`FinanceInvoice`.`Value`)) FROM `FinanceInvoice` WHERE (`FinanceInvoice`.`Time` <= `DateEnd`) '.
+        'SQL' => '(SELECT ROUND(SUM(`FinanceInvoice`.`Value` * `FinanceInvoice`.`Direction`)) FROM `FinanceInvoice` WHERE (`FinanceInvoice`.`Time` <= `DateEnd`) '.
         'AND (`FinanceInvoice`.`Time` >= `DateStart`))'),
     ),

trunk/Application/Version.php

@@ -1 +1 @@
 <?php
 
-$Revision = 719; // Subversion revision
-$DatabaseRevision = 719; // SQL structure revision
+$Revision = 720; // Subversion revision
+$DatabaseRevision = 720; // SQL structure revision
 $ReleaseTime = strtotime('2015-01-02');

trunk/Common/Setup/Updates.php

@@ -1055 +1055 @@
   $Manager->Execute('UPDATE `FinanceOperation` SET `Direction` = -1 WHERE `Value` < 0 ;');
   $Manager->Execute('UPDATE `FinanceOperation` SET `Value` = -`Value` WHERE `Value` < 0 ;');
-
+  // Set missing FinanceOperation DocumentLine according BillCode 
+  $Manager->Execute('UPDATE `FinanceOperation` SET `DocumentLine` = 1 WHERE (`BillCode` LIKE "PP%") AND (`DocumentLine` IS NULL)');
+  $Manager->Execute('UPDATE `FinanceOperation` SET `DocumentLine` = 2 WHERE (`BillCode` LIKE "VP%") AND (`DocumentLine` IS NULL)');
+  $Manager->Execute('UPDATE `FinanceOperation` SET `DocumentLine` = 3 WHERE (`BillCode` LIKE "BV%") AND (`DocumentLine` IS NULL)');
+  $Manager->Execute('UPDATE `FinanceOperation` SET `DocumentLine` = 4 WHERE (`BillCode` LIKE "PR%") AND (`DocumentLine` IS NULL)');
+  
   // IS menu item
   $DbResult = $Manager->Execute('SELECT `Id` FROM `MenuItem` WHERE `Name`="Příjmy a výdaje"');
@@ -1086 +1091 @@
       "VALUES (NULL , 'Výdej z účtu', ".$DbRow['Id'].", '".$ActionId."', '1');");
   }
+}
+
+function UpdateTo720($Manager)
+{
+        $Manager->Execute('ALTER TABLE `FinanceInvoice` ADD `Direction` INT NOT NULL AFTER `TimePayment`;');
+        $Manager->Execute('UPDATE `FinanceInvoice` SET `Direction` = 1 WHERE `Value` >= 0 ;');
+        $Manager->Execute('UPDATE `FinanceInvoice` SET `Direction` = -1 WHERE `Value` < 0 ;');
+        $Manager->Execute('UPDATE `FinanceInvoice` SET `Value` = -`Value` WHERE `Value` < 0 ;');
+        // Set missing FinanceInvoice DocumentLine according BillCode
+        $Manager->Execute('UPDATE `FinanceInvoice` SET `DocumentLine` = 5 WHERE (`BillCode` LIKE "PF%") AND (`DocumentLine` IS NULL)');
+        $Manager->Execute('UPDATE `FinanceInvoice` SET `DocumentLine` = 6 WHERE (`BillCode` LIKE "VF%") AND (`DocumentLine` IS NULL)');
+        
+        // IS menu item
+        $DbResult = $Manager->Execute('SELECT `Id` FROM `MenuItem` WHERE `Name`="Závazky a pohledávky"');
+        if($DbResult->num_rows > 0)
+        {
+                $DbRow = $DbResult->fetch_assoc();
+                $Manager->Execute('INSERT INTO `Action` (`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,
+    `Group` ,`Icon` ,`PermissionOperation` ,`Enable`) VALUES (
+    NULL , "", "Příjaté", "1", "/is/?t=FinanceInvoiceIn&a=list", NULL , NULL , NULL , "1");');
+                $ActionId = $Manager->Database->insert_id;
+                $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
+                                "VALUES (NULL , 'Přijaté', ".$DbRow['Id'].", '".$ActionId."', '1');");
+                
+                $Manager->Execute('INSERT INTO `Action` (`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,
+    `Group` ,`Icon` ,`PermissionOperation` ,`Enable`) VALUES (
+    NULL , "", "Vydané", "1", "/is/?t=FinanceInvoiceOut&a=list", NULL , NULL , NULL , "1");');
+                $ActionId = $Manager->Database->insert_id;
+                $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
+                                "VALUES (NULL , 'Vydané', ".$DbRow['Id'].", '".$ActionId."', '1');");
+        }
 }
 
@@ -1145 +1181 @@
       715 => array('Revision' => 718, 'Function' => 'UpdateTo718'),
       718 => array('Revision' => 719, 'Function' => 'UpdateTo719'),
+        719 => array('Revision' => 720, 'Function' => 'UpdateTo720'),
     ));
   }

trunk/Modules/Finance/Bill.php

@@ -73 +73 @@
 
     // If negative value => switch sides
-    if($Invoice['Value'] < 0)
+    if($Invoice['Direction'] == -1)
     {
         $Subject = $SubjectTo;
         $SubjectTo = $SubjectFrom;
         $SubjectFrom = $Subject;
-        foreach($InvoiceItems as $Index => $Item)
-        {
-                $InvoiceItems[$Index]['Price'] = -$InvoiceItems[$Index]['Price'];
-        }
     }
 
@@ -159 +155 @@
     $MainSubjectAccount = $DbResult->fetch_assoc();
 
-    //$Dodavka = array();
-    //$DbResult = $this->Database->select('FinanceInvoiceItem', '*', 'FinanceInvoice='.$BillId);
-    //while($Item = $DbResult->fetch_assoc())
-    //{
-    //  $InvoiceItems[$Item['Id']] = $Item;
-    //}
     $BooleanText = array('Ne', 'Ano');
     if($Operation['Direction'] == -1)

trunk/Modules/Finance/Finance.php

@@ -186 +186 @@
     while($Member = $DbResult->fetch_assoc())
     {
-      $DbResult2 = $this->Database->query('SELECT ((SELECT COALESCE(SUM(-Value*Direction), 0) FROM FinanceOperation '.
-          'WHERE Subject='.$Member['Subject'].') + (SELECT COALESCE(SUM(-Value), 0) FROM FinanceInvoice '.
-          'WHERE Subject='.$Member['Subject'].')) as Cash');
+      $DbResult2 = $this->Database->query('SELECT ((SELECT COALESCE(-SUM(Value*Direction), 0) FROM FinanceOperation '.
+          'WHERE Subject='.$Member['Subject'].') + (SELECT COALESCE(-SUM(Value*Direction), 0) FROM FinanceInvoice '.
+          'WHERE Subject='.$Member['Subject'].')) AS Cash');
       $Cash = $DbResult2->fetch_row();
       $Cash = $Cash[0];
@@ -277 +277 @@
       'DefaultSortOrder' => 1,
       'Items' => array(
-        'Direction' => array('Type' => 'TFinanceDirection', 'Caption' => 'Směr', 'Default' => '1'),
+        'Direction' => array('Type' => 'TFinanceOperationDirection', 'Caption' => 'Směr', 'Default' => '1'),
         'DocumentLine' => array('Type' => 'TDocumentLine', 'Caption' => 'Dokladová řada', 'Default' => ''),
         'BillCode' => array('Type' => 'String', 'Caption' => 'Označení', 'Default' => ''),
@@ -337 +337 @@
     $this->System->FormManager->Classes['FinanceAccountOut']['Items']['Treasury']['Hidden'] = true;
     
-    $this->System->FormManager->RegisterFormType('TFinanceDirection', array(
+    $this->System->FormManager->RegisterFormType('TFinanceOperationDirection', array(
       'Type' => 'Enumeration',
       'States' => array(-1 => 'Výdej', 1 => 'Příjem'),
@@ -347 +347 @@
       'DefaultSortOrder' => 1,
       'Items' => array(
-        'DocumentLine' => array('Type' => 'TDocumentLine', 'Caption' => 'Dokladová řada', 'Default' => ''),
+        'Direction' => array('Type' => 'TFinanceInvoiceDirection', 'Caption' => 'Směr', 'Default' => '1'),
+        'DocumentLine' => array('Type' => 'TDocumentLine', 'Caption' => 'Dokladová řada', 'Default' => ''),
         'BillCode' => array('Type' => 'String', 'Caption' => 'Označení', 'Default' => ''),
         'Subject' => array('Type' => 'TSubject', 'Caption' => 'Subjekt', 'Default' => ''),
@@ -367 +368 @@
       'BeforeInsert' => array($this, 'BeforeInsertFinanceOperation'),
     ));
+    $this->System->FormManager->RegisterClass('FinanceInvoiceIn', $this->System->FormManager->Classes['FinanceInvoice']);
+    $this->System->FormManager->Classes['FinanceInvoiceIn']['Title'] = 'Přijaté faktury';
+    $this->System->FormManager->Classes['FinanceInvoiceIn']['Items']['Direction']['Default'] = -1;
+    $this->System->FormManager->Classes['FinanceInvoiceIn']['Items']['Direction']['Hidden'] = true;
+    $this->System->FormManager->Classes['FinanceInvoiceIn']['Items']['Direction']['Filter'] = true;
+    $this->System->FormManager->Classes['FinanceInvoiceIn']['Items']['DocumentLine']['Default'] = DOC_LINE_INVOICE_IN;
+    $this->System->FormManager->Classes['FinanceInvoiceIn']['Items']['DocumentLine']['Hidden'] = true;
+    $this->System->FormManager->Classes['FinanceInvoiceIn']['Items']['DocumentLine']['Filter'] = true;
+        
+    $this->System->FormManager->RegisterClass('FinanceInvoiceOut', $this->System->FormManager->Classes['FinanceInvoice']);
+    $this->System->FormManager->Classes['FinanceInvoiceOut']['Title'] = 'Vydané faktury';
+    $this->System->FormManager->Classes['FinanceInvoiceOut']['Items']['Direction']['Default'] = 1;
+    $this->System->FormManager->Classes['FinanceInvoiceOut']['Items']['Direction']['Hidden'] = true;
+    $this->System->FormManager->Classes['FinanceInvoiceOut']['Items']['Direction']['Filter'] = true;
+    $this->System->FormManager->Classes['FinanceInvoiceOut']['Items']['DocumentLine']['Default'] = DOC_LINE_INVOICE_OUT;
+    $this->System->FormManager->Classes['FinanceInvoiceOut']['Items']['DocumentLine']['Hidden'] = true;
+    $this->System->FormManager->Classes['FinanceInvoiceOut']['Items']['DocumentLine']['Filter'] = true;
+
+    $this->System->FormManager->RegisterFormType('TFinanceInvoiceDirection', array(
+                'Type' => 'Enumeration',
+                'States' => array(-1 => 'Příjem', 1 => 'Výdej'),
+    ));
+    
     $this->System->FormManager->RegisterClass('Company', array(
       'Title' => 'Firma',

trunk/Modules/Finance/Manage.php

@@ -50 +50 @@
   {
     if(!$this->System->User->CheckPermission('Finance', 'Manage')) return('Nemáte oprávnění');
-    $SQL = 'SELECT Member.*, MemberPayment.MonthlyTotal AS Monthly, '.
-      'MemberPayment.Cash AS Cash, '.
-      '(SELECT GROUP_CONCAT(Service.Name) FROM ServiceCustomerRel LEFT JOIN Service '.
-      'ON Service.Id=ServiceCustomerRel.Service WHERE ServiceCustomerRel.Customer=Member.Id AND '.
-      'ServiceCustomerRel.Action IS NULL) AS ServicesNextMonth, '.
-      'UNIX_TIMESTAMP(Member.BillingPeriodLastDate) AS LastDate, Subject.Name AS SubjectName, '.
-      'FinanceBillingPeriod.Name AS BillingPeriodName '.
-      'FROM MemberPayment JOIN Member ON Member.Id=MemberPayment.Member JOIN Subject '.
-      'ON Subject.Id=Member.Subject LEFT JOIN FinanceBillingPeriod ON '.
-      'FinanceBillingPeriod.Id=Member.BillingPeriodNext WHERE (Member.Blocked = 0)'.
-      'AND (Member.BillingPeriodNext > 1) AND (MemberPayment.MonthlyTotal != 0)';
+    $SQL = 'SELECT `Member`.*, `MemberPayment`.`MonthlyTotal` AS `Monthly`, '.
+      '`MemberPayment`.`Cash` AS `Cash`, '.
+      '(SELECT GROUP_CONCAT(`Service`.`Name`) FROM `ServiceCustomerRel` LEFT JOIN `Service` '.
+      'ON `Service`.`Id`=`ServiceCustomerRel`.`Service` WHERE `ServiceCustomerRel`.`Customer`=`Member`.`Id` AND '.
+      '`ServiceCustomerRel`.`Action` IS NULL) AS `ServicesNextMonth`, '.
+      'UNIX_TIMESTAMP(`Member`.`BillingPeriodLastDate`) AS `LastDate`, `Subject`.`Name` AS `SubjectName`, '.
+      '`FinanceBillingPeriod`.`Name` AS `BillingPeriodName` '.
+      'FROM `MemberPayment` JOIN `Member` ON `Member`.`Id`=`MemberPayment`.`Member` JOIN `Subject` '.
+      'ON `Subject`.`Id`=`Member`.`Subject` LEFT JOIN `FinanceBillingPeriod` ON '.
+      '`FinanceBillingPeriod`.`Id`=`Member`.`BillingPeriodNext` WHERE (`Member`.`Blocked` = 0)'.
+      'AND (`Member`.`BillingPeriodNext` > 1) AND (`MemberPayment`.`MonthlyTotal` != 0)';
 
     $DbResult = $this->Database->query('SELECT COUNT(*) FROM ('.$SQL.') AS T');
@@ -100 +100 @@
   }
 
-  function InsertInvoice($Subject, $TimeCreation, $TimeDue, $Items,
+  function InsertInvoice($Subject, $TimeCreation, $TimeDue, $Direction, $Items,
   $DocumentLine, $PeriodFrom, $PeriodTo)
   {
@@ -109 +109 @@
     $SumValue = 0;
     foreach($Items as $Item)
-        $SumValue = $SumValue + ceil($Item['Price'] * $Item['Quantity']);
+      $SumValue = $SumValue + ceil($Item['Price'] * $Item['Quantity']);    
     $this->Database->insert('FinanceInvoice', array(
-          'Subject' => $Subject, 'Time' => TimeToMysqlDateTime($TimeCreation),
-          'TimeDue' => TimeToMysqlDateTime($TimeDue), 'Value' => $SumValue, 'BillCode' => $BillCode,
-          'PeriodFrom' => TimeToMysqlDate($PeriodFrom), 'PeriodTo' => TimeToMysqlDate($PeriodTo),
-          'Generate' => 1, 'DocumentLine' => $DocumentLine));
+      'Subject' => $Subject, 'Time' => TimeToMysqlDateTime($TimeCreation),
+      'TimeDue' => TimeToMysqlDateTime($TimeDue), 'Value' => $SumValue, 
+        'Direction' => $Direction, 'BillCode' => $BillCode,
+      'PeriodFrom' => TimeToMysqlDate($PeriodFrom), 'PeriodTo' => TimeToMysqlDate($PeriodTo),
+      'Generate' => 1, 'DocumentLine' => $DocumentLine));
     $InvoiceId = $this->Database->insert_id;
     foreach($Items as $Item)
@@ -130 +131 @@
 
     // Generuj účetní položky
-    $DbResult = $this->Database->query('SELECT Member.*, MemberPayment.MonthlyTotal AS MonthlyTotal, '.
-      'UNIX_TIMESTAMP(Member.BillingPeriodLastDate), Subject.Name AS SubjectName,'.
-      'MemberPayment.MonthlyPlus AS MonthlyPlus '.
-      'FROM MemberPayment JOIN Member ON Member.Id=MemberPayment.Member '.
-      'JOIN Subject ON Subject.Id=Member.Subject');
+    $DbResult = $this->Database->query('SELECT `Member`.*, `MemberPayment`.`MonthlyTotal` AS `MonthlyTotal`, '.
+      'UNIX_TIMESTAMP(`Member`.`BillingPeriodLastDate`), `Subject`.`Name` AS `SubjectName`,'.
+      '`MemberPayment`.`MonthlyPlus` AS `MonthlyPlus` '.
+      'FROM `MemberPayment` JOIN `Member` ON `Member`.`Id`=`MemberPayment`.`Member` '.
+      'JOIN `Subject` ON `Subject`.`Id`=`Member`.`Subject`');
     while($Member = $DbResult->fetch_assoc())
     {
@@ -163 +164 @@
         }
         $PayPerPeriod = $MonthlyTotal * $Period['MonthCount'];
+        // We can't produce negative invoice except storno invoice.
+        // TODO: In case of negative invoice it is not sufficient to reverse invoicing direction  
+        // Other subject should invoice only possitive items. Negative items should be somehow removed. 
+        if($MonthlyTotal >= 0)
+                $Direction = 1; // Standard out invoice
+          else {
+                $Direction = -1; // In case of negative total value generate reverse invoice for other subject
+                foreach($InvoiceItems as $Index => $Item)
+                        $InvoiceItems[$Index]['Price'] = -$Item['Price'];
+          }
+        
+        
         if($PayPerPeriod != 0)
         {
@@ -168 +181 @@
           $Output .= $TimePeriodText.': '.$MonthlyTotal.' * '.$Period['MonthCount'].' = '.$PayPerPeriod.'<br />';
           $this->InsertInvoice($Member['Subject'], time(), time() + 3600 * 24 * INVOICE_DUE_DAYS,
-            $InvoiceItems, DOC_LINE_INVOICE_OUT, $Period['From'], $Period['To']);
+            $Direction, $InvoiceItems, DOC_LINE_INVOICE_OUT, $Period['From'], $Period['To']);
 
           $Output .= $this->SendPaymentEmail($Member['Id']);
@@ -227 +240 @@
     $Output .= $Finance->LoadTariffs();
     $Output .= $Finance->RecalculateMemberPayment();
-
-    // Celkovy prehled
-    $DbResult = $this->Database->query('SELECT SUM(Product.SellPrice) AS Price FROM StockSerialNumber '.
-      'JOIN Product ON StockSerialNumber.Product = Product.Id WHERE (StockSerialNumber.TimeElimination IS NULL) AND (StockSerialNumber.Segment IS NOT NULL)');
-    $Row = $DbResult->fetch_row();
-    $TotalDeviceCost = $Row[0];
-    $Output .= 'Celková cena zařízení: Zařízení('.$TotalDeviceCost;
 
     $DbResult = $this->Database->query('SELECT SUM(NetworkDevice) FROM MemberPayment');
@@ -279 +285 @@
       $Output .= 'Aktualizuji služby....<br>';
       $this->TableUpdatePeriod('Service');
+      
       // Update customer service selections
       $Output .= 'Aktualizuji výběr služeb zákazníků....<br>';
@@ -343 +350 @@
         '<th style="border-style: solid; border-width: 1px; padding: 1px 5px 1px 5px; text-align: center; font-weight: bold;">Částka [Kč]</th></tr>'."\n";
       $DbResult = $this->Database->query('SELECT T1.* FROM ((SELECT Text, Time, (Value*Direction) AS Value, File FROM FinanceOperation WHERE (Subject='.$Member['Subject'].')) UNION ALL '.
-        '(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='.
+        '(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='.
         $Member['Subject'].')) ORDER BY Time DESC) AS T1 WHERE (T1.Time > "'.$Member['BillingPeriodLastDate'].'")');
       while($DbRow = $DbResult->fetch_assoc())

trunk/Modules/Finance/UserState.php

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

trunk/Modules/Finance/Zivnost.php

@@ -27 +27 @@
     $Balance['Spend']['End'] = $Row[0] + 0;
 
-    $DbResult = $this->Database->query('SELECT SUM(Value) FROM FinanceInvoice WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Value > 0)');
+    $DbResult = $this->Database->query('SELECT SUM(Value*Direction) FROM FinanceInvoice WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Direction = 1)');
     $Row = $DbResult->fetch_array();
     $Balance['Claims']['Start'] = $Row[0] + 0;
-    $DbResult = $this->Database->query('SELECT SUM(Value) FROM FinanceInvoice WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Value > 0)');
+    $DbResult = $this->Database->query('SELECT SUM(Value*Direction) FROM FinanceInvoice WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Direction = 1)');
     $Row = $DbResult->fetch_array();
     $Balance['Claims']['End'] = $Row[0] + 0;
 
-    $DbResult = $this->Database->query('SELECT SUM(-Value) FROM FinanceInvoice WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Value < 0)');
+    $DbResult = $this->Database->query('SELECT -SUM(Value*Direction) FROM FinanceInvoice WHERE (Time < "'.TimeToMysqlDateTime($StartTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Direction = -1)');
     $Row = $DbResult->fetch_array();
     $Balance['Liabilities']['Start'] = $Row[0] + 0;
-    $DbResult = $this->Database->query('SELECT SUM(-Value) FROM FinanceInvoice WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Value < 0)');
+    $DbResult = $this->Database->query('SELECT -SUM(Value*Direction) FROM FinanceInvoice WHERE (Time <= "'.TimeToMysqlDateTime($EndTime).'") AND (Time >= "'.TimeToMysqlDateTime($this->StartEvidence).'") AND (Direction = -1)');
     $Row = $DbResult->fetch_array();
     $Balance['Liabilities']['End'] = $Row[0] + 0;
@@ -206 +206 @@
         $DbResult = $this->Database->query('SELECT *, (SELECT GROUP_CONCAT(Description SEPARATOR ",") FROM FinanceInvoiceItem WHERE FinanceInvoiceItem.FinanceInvoice = FinanceInvoice.Id) AS Text '.
           'FROM FinanceInvoice JOIN Subject ON Subject.Id = FinanceInvoice.Subject '.
-          'WHERE (Value > 0) AND (FinanceInvoice.Time >= "'.$Year['DateStart'].
+          'WHERE (Direction = 1) AND (FinanceInvoice.Time >= "'.$Year['DateStart'].
           '") AND (FinanceInvoice.Time <= "'.$Year['DateEnd'].'") ORDER BY Time');
         while($Row = $DbResult->fetch_array())
         {
           if($Row['TimePayment'] == '0000-00-00 00:00:00') $Row['TimePayment'] = '&nbsp;';
-          $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.$Row['BillCode'].'</td><td>'.$Row['Name'].'</td><td>'.$Row['Text'].'</td><td>'.$Row['Value'].'</td></tr>';
+          $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.$Row['BillCode'].
+            '</td><td>'.$Row['Name'].'</td><td>'.$Row['Text'].'</td><td>'.($Row['Value'] * $Row['Direction']).'</td></tr>';
           $Total += $Row['Value'];
         }
@@ -228 +229 @@
         $DbResult = $this->Database->query('SELECT *, (SELECT GROUP_CONCAT(Description SEPARATOR ",") FROM FinanceInvoiceItem WHERE FinanceInvoiceItem.FinanceInvoice = FinanceInvoice.Id) AS Text '.
           'FROM FinanceInvoice JOIN Subject ON Subject.Id = FinanceInvoice.Subject '.
-          'WHERE (Value < 0) AND (FinanceInvoice.Time >= "'.$Year['DateStart'].
+          'WHERE (Direction = -1) AND (FinanceInvoice.Time >= "'.$Year['DateStart'].
           '") AND (FinanceInvoice.Time <= "'.$Year['DateEnd'].'") ORDER BY Time');
         while($Row = $DbResult->fetch_array())
@@ -234 +235 @@
           if($Row['TimePayment'] == '0000-00-00 00:00:00') $Row['TimePayment'] = '&nbsp;';
           $Row['Value'] = $Row['Value'] * -1;
-          $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.$Row['BillCode'].'</td><td>'.$Row['Name'].'</td><td>'.$Row['Text'].'</td><td>'.$Row['Value'].'</td></tr>';          
+          $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.$Row['BillCode'].
+            '</td><td>'.$Row['Name'].'</td><td>'.$Row['Text'].'</td><td>'.($Row['Value'] * $Row['Direction']).'</td></tr>';          
           $Total += $Row['Value'];
         }
@@ -245 +247 @@
         $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>';
         $DbResult = $this->Database->query('SELECT Id, Name, 0 AS Cash, '.
-        '(SELECT SUM(T1.Value) FROM FinanceInvoice AS T1 WHERE (T1.Subject = Subject.Id) AND (T1.Value > 0)) as Claims, '.
-        '(SELECT SUM(T2.Value) FROM FinanceInvoice AS T2 WHERE (T2.Subject = Subject.Id) AND (T2.Value > 0) AND (TimePayment IS NULL)) as OpenedClaims, '.
-        '(SELECT -SUM(T3.Value) FROM FinanceInvoice AS T3 WHERE (T3.Subject = Subject.Id) AND (T3.Value  < 0)) as Liabilities, '.
-        '(SELECT -SUM(T4.Value) FROM FinanceInvoice AS T4 WHERE (T4.Subject = Subject.Id) AND (T4.Value < 0) AND (TimePayment IS NULL)) AS OpenedLiabilities, '.
+        '(SELECT SUM(T1.Value*T1.Direction) FROM FinanceInvoice AS T1 WHERE (T1.Subject = Subject.Id) AND (T1.Direction = 1)) as Claims, '.
+        '(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, '.
+        '(SELECT -SUM(T3.Value*T3.Direction) FROM FinanceInvoice AS T3 WHERE (T3.Subject = Subject.Id) AND (T3.Direction = -1)) as Liabilities, '.
+        '(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, '.
         '(SELECT SUM(T5.Value*T5.Direction) FROM FinanceOperation AS T5 WHERE (T5.Subject = Subject.Id) AND (T5.Direction = 1)) AS Gains, '.
         '(SELECT SUM(T6.Value*T6.Direction) FROM FinanceOperation AS T6 WHERE (T6.Subject = Subject.Id) AND (T6.Direction = -1)) AS Spends '.
@@ -287 +289 @@
         $Output .= '<table style="font-size: smaller;" border="1" cellspacing="0" cellpadding="3">';
         $Output .= '<tr><th>Datum vytvoření</th><th>Datum zaplacení</th><th>Název</th><th>Hodnota [Kč]</th><th>Doklad</th></tr>';
-        $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');
+        $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');
         while($Row = $DbResult->fetch_array())
         {
           $Output .= '<tr><td>'.HumanDate($Row['Time']).'</td><td>'.HumanDate($Row['TimePayment']).
-            '</td><td>'.$Row['Text'].'</td><td>'.($Row['Value']).'</td><td>'.$Row['BillCode'].'</td></tr>';
+            '</td><td>'.$Row['Text'].'</td><td>'.ceil($Row['Value'] * $Row['Direction']).'</td><td>'.$Row['BillCode'].'</td></tr>';
         }
         $Output .= '</table></td></tr></table>';
@@ -305 +309 @@
         $Output .= '</table>';*/
 
-        $DbResult = $this->Database->query('SELECT Id, 0 AS Cash, (SELECT SUM(FinanceInvoice.Value) '.
-          'FROM FinanceInvoice WHERE FinanceInvoice.Subject = Subject.Id AND '.
-          'FinanceInvoice.Value > 0) as Claims, (SELECT SUM(FinanceInvoice.Value) '.
-          'FROM FinanceInvoice WHERE FinanceInvoice.Subject = Subject.Id AND '.
-          'FinanceInvoice.Value > 0 AND TimePayment IS NULL) as OpenedClaims, '.
-          '(SELECT SUM(FinanceInvoice.Value) FROM FinanceInvoice '.
-          'WHERE FinanceInvoice.Subject = Subject.Id AND FinanceInvoice.Value < 0) AS '.
+        $DbResult = $this->Database->query('SELECT Id, 0 AS Cash, (SELECT SUM(FinanceInvoice.Value * FinanceInvoice.Direction) '.
+          'FROM FinanceInvoice WHERE (FinanceInvoice.Subject = Subject.Id) AND '.
+          '(FinanceInvoice.Direction = 1)) as Claims, (SELECT SUM(FinanceInvoice.Value * FinanceInvoice.Direction) '.
+          'FROM FinanceInvoice WHERE (FinanceInvoice.Subject = Subject.Id) AND ('.
+          'FinanceInvoice.Direction = 1) AND (TimePayment IS NULL)) as OpenedClaims, '.
+          '(SELECT SUM(FinanceInvoice.Value * FinanceInvoice.Direction) FROM FinanceInvoice '.
+          'WHERE (FinanceInvoice.Subject = Subject.Id) AND (FinanceInvoice.Direction = -1)) AS '.
           'Liabilities, (SELECT SUM(FinanceInvoice.Value) FROM FinanceInvoice '.
-          'WHERE FinanceInvoice.Subject = Subject.Id AND FinanceInvoice.Value < 0 '.
-          'AND TimePayment IS NULL) as OpenedLiabilities, (SELECT SUM(FinanceOperation.Value*FinanceOperation.Direction) '.
+          'WHERE (FinanceInvoice.Subject = Subject.Id) AND (FinanceInvoice.Direction = -1) '.
+          'AND (TimePayment IS NULL)) AS OpenedLiabilities, '.
+                '(SELECT SUM(FinanceOperation.Value*FinanceOperation.Direction) '.
           'FROM FinanceOperation WHERE FinanceOperation.Subject = Subject.Id AND FinanceOperation.Direction = 1) '.
           'AS Gains, (SELECT SUM(FinanceOperation.Value*FinanceOperation.Direction) FROM FinanceOperation WHERE '.

trunk/Modules/Subject/Subject.php

@@ -37 +37 @@
         'Payment' => array('Type' => 'Float', 'Caption' => 'Placení', 'Default' => '',
           'ReadOnly' => true, 'Suffix' => 'Kč', 'SQL' => 'IFNULL((SELECT SUM(`FinanceOperation`.`Value` * `FinanceOperation`.`Direction`) FROM `FinanceOperation` '.
-          'WHERE `FinanceOperation`.`Subject`=#Id), 0) - IFNULL((SELECT SUM(`FinanceInvoice`.`Value`) FROM `FinanceInvoice` '.
+          'WHERE `FinanceOperation`.`Subject`=#Id), 0) - IFNULL((SELECT SUM(`FinanceInvoice`.`Value` * `FinanceInvoice`.`Direction`) FROM `FinanceInvoice` '.
           'WHERE `FinanceInvoice`.`Subject`=#Id), 0)'),
       ),