Changeset 747 for trunk/Application/FormClasses.php

Timestamp:

Sep 21, 2015, 12:12:26 AM (9 years ago)

Author:

chronos

Message:

• Modified: Removed columns ValueSign and Direction from FinanceOperation and FinanceInvoice tables. For both tables Value field will be computed as sum from items multiplied by ValueSign of selected group.

This solves problem with inconsistency between invoice and invoice items values and inconsistency between selected document line and incorrect positive or negative values.

File:

• trunk/Application/FormClasses.php (modified) (2 diffs)

trunk/Application/FormClasses.php

@@ -52 +52 @@
     'Items' => array(
       'DocumentLine' => array('Type' => 'TDocumentLine', 'Caption' => 'Dokladová řada', 'Default' => ''),
-      'Name' => array('Type' => 'String', 'Caption' => 'Název', 'Default' => ''),      
+      'Name' => array('Type' => 'String', 'Caption' => 'Název', 'Default' => ''),
     ),
   ),
@@ -78 +78 @@
         'ReadOnly' => true),
       'Income' => array('Type' => 'Integer', 'Caption' => 'Příjmy', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
-        'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value` * `FinanceOperation`.`ValueSign`)) FROM `FinanceOperation` WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
-        'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperation`.`ValueSign` = 1))'),
+        'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
+        'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id`=`FinanceOperation`.`Group` '.
+        'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
+        'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperationGroup`.`ValueSign` = 1))'),
       'Spending' => array('Type' => 'Integer', 'Caption' => 'Výdaje', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
-        'SQL' => '(SELECT -ROUND(SUM(`FinanceOperation`.`Value` * `FinanceOperation`.`ValueSign`)) FROM `FinanceOperation` WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
-        'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperation`.`ValueSign` = -1))'),
+        'SQL' => '(SELECT -ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
+        'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id`=`FinanceOperation`.`Group` '.
+        'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
+        'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperationGroup`.`ValueSign` = -1))'),
       'OperationBalance' => array('Type' => 'Integer', 'Caption' => 'Zisk', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
-        'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value` * `FinanceOperation`.`ValueSign`)) FROM `FinanceOperation` WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
+        'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
+        'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
         'AND (`FinanceOperation`.`Time` >= `DateStart`))'),
       'TaxIncome' => array('Type' => 'Integer', 'Caption' => 'Daňové příjmy', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
-        'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value` * `FinanceOperation`.`ValueSign`)) FROM `FinanceOperation` WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
-        'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperation`.`Taxable` = 1) AND (`FinanceOperation`.`ValueSign` = 1))'),
+        'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
+        'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id`=`FinanceOperation`.`Group` '.
+        'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
+        'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperation`.`Taxable` = 1) AND (`FinanceOperationGroup`.`ValueSign` = 1))'),
       'TaxSpending' => array('Type' => 'Integer', 'Caption' => 'Daňové výdaje', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
-        'SQL' => '(SELECT -ROUND(SUM(`FinanceOperation`.`Value` * `FinanceOperation`.`ValueSign`)) FROM `FinanceOperation` WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
-        'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperation`.`Taxable` = 1) AND (`FinanceOperation`.`ValueSign` = -1))'),
+        'SQL' => '(SELECT -ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
+        'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id`=`FinanceOperation`.`Group` '.
+        'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
+        'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperation`.`Taxable` = 1) AND (`FinanceOperationGroup`.`ValueSign` = -1))'),
       'TaxBase' => array('Type' => 'Integer', 'Caption' => 'Základ daně', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
-        'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value` * `FinanceOperation`.`ValueSign`)) FROM `FinanceOperation` WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
+        'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
+        'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
         '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` * `FinanceInvoice`.`ValueSign`)) FROM `FinanceInvoice` WHERE (`FinanceInvoice`.`Time` <= `DateEnd`) '.
-        'AND (`FinanceInvoice`.`Time` >= `DateStart`) AND (`FinanceInvoice`.`ValueSign` = 1))'),
+        'SQL' => '(SELECT ROUND(SUM(`FinanceInvoice`.`Value`)) FROM `FinanceInvoice` '.
+        'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id`=`FinanceInvoice`.`Group` '.
+        'WHERE (`FinanceInvoice`.`Time` <= `DateEnd`) '.
+        'AND (`FinanceInvoice`.`Time` >= `DateStart`) AND (`FinanceInvoiceGroup`.`ValueSign` = 1))'),
       'Liabilities' => array('Type' => 'Integer', 'Caption' => 'Závazky', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
-        'SQL' => '(SELECT -ROUND(SUM(`FinanceInvoice`.`Value` * `FinanceInvoice`.`ValueSign`)) FROM `FinanceInvoice` WHERE (`FinanceInvoice`.`Time` <= `DateEnd`) '.
-        'AND (`FinanceInvoice`.`Time` >= `DateStart`) AND (`FinanceInvoice`.`ValueSign` = -1))'),
+        'SQL' => '(SELECT -ROUND(SUM(`FinanceInvoice`.`Value`)) FROM `FinanceInvoice` '.
+        'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id`=`FinanceInvoice`.`Group` '.
+        'WHERE (`FinanceInvoice`.`Time` <= `DateEnd`) '.
+        'AND (`FinanceInvoice`.`Time` >= `DateStart`) AND (`FinanceInvoiceGroup`.`ValueSign` = -1))'),
       'InvoiceBalance' => array('Type' => 'Integer', 'Caption' => 'Fakturační rozdíl', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
-        'SQL' => '(SELECT ROUND(SUM(`FinanceInvoice`.`Value` * `FinanceInvoice`.`ValueSign`)) FROM `FinanceInvoice` WHERE (`FinanceInvoice`.`Time` <= `DateEnd`) '.
+        'SQL' => '(SELECT ROUND(SUM(`FinanceInvoice`.`Value`)) FROM `FinanceInvoice` WHERE (`FinanceInvoice`.`Time` <= `DateEnd`) '.
         'AND (`FinanceInvoice`.`Time` >= `DateStart`))'),
     ),