Changeset 967 for trunk/Modules/Finance/Finance.php

Timestamp:

Mar 6, 2024, 11:47:49 PM (10 months ago)

Author:

chronos

Message:

• Added: Monthly finance report.

File:

• trunk/Modules/Finance/Finance.php (modified) (1 diff)

trunk/Modules/Finance/Finance.php

@@ -548 +548 @@
       //'AfterInsert' => array($this, 'AfterInsertFinanceYear'),
     ));
+    $this->System->FormManager->RegisterClass('FinanceMonthReport', array(
+      'Title' => 'Měsíční výkaz',
+      'Table' => 'FinanceMonthReport',
+      'SQL' => 'SELECT 0 AS `Id`, m1 AS `DateStart`, LAST_DAY(m1) AS `DateEnd` FROM '.
+      '(SELECT ((SELECT MIN(`DateStart`) FROM `FinanceYear`) - INTERVAL DAYOFMONTH((SELECT MIN(`DateStart`) FROM `FinanceYear`)) - 1 DAY) '.
+      '+ INTERVAL m MONTH AS m1 FROM ('.
+      'SELECT @rownum := @rownum + 1 AS m FROM '.
+      '(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t1,'.
+      '(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2,'.
+      '(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t3,'.
+      '(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t4,'.
+      '(SELECT @rownum := -1) t0'.
+      ') d1'.
+      ') d2 '.
+      'WHERE m1 <= (SELECT MAX(`DateEnd`) FROM `FinanceYear`)',
+      'DefaultSortColumn' => 'DateStart',
+      'DefaultSortOrder' => 1,
+      'Items' => array(
+        'DateStart' => array('Type' => 'Date', 'Caption' => 'Měsíc', 'Default' => '', 'ReadOnly' => true),
+        'Income' => array('Type' => 'Integer', 'Caption' => 'Evidenční příjmy', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
+          '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' => 'Evidenční výdaje', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
+          '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' => 'Evidenční zisk', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
+          'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
+          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) '.
+          'AND (`FinanceOperation`.`Time` >= `DateStart`))'),
+        'RealIncome' => array('Type' => 'Integer', 'Caption' => 'Skutečné příjmy', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
+          'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
+          'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id`=`FinanceOperation`.`Group` '.
+          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) AND (`FinanceOperation`.`Subject` != '.$this->Finance->MainSubject.')'.
+          'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperationGroup`.`ValueSign` = 1))'),
+        'RealSpending' => array('Type' => 'Integer', 'Caption' => 'Skutečné výdaje', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
+          'SQL' => '(SELECT -ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
+          'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id`=`FinanceOperation`.`Group` '.
+          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) AND (`FinanceOperation`.`Subject` != '.$this->Finance->MainSubject.')'.
+          'AND (`FinanceOperation`.`Time` >= `DateStart`) AND (`FinanceOperationGroup`.`ValueSign` = -1))'),
+        'RealOperationBalance' => array('Type' => 'Integer', 'Caption' => 'Skutečný zisk', 'Default' => '0', 'Suffix' => 'Kč', 'ReadOnly' => true,
+          'SQL' => '(SELECT ROUND(SUM(`FinanceOperation`.`Value`)) FROM `FinanceOperation` '.
+          'WHERE (`FinanceOperation`.`Time` <= `DateEnd`) AND (`FinanceOperation`.`Subject` != '.$this->Finance->MainSubject.')'.
+          '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`)) 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`)) 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`)) 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`)) 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`)) 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`)) FROM `FinanceInvoice` WHERE (`FinanceInvoice`.`Time` <= `DateEnd`) '.
+          'AND (`FinanceInvoice`.`Time` >= `DateStart`))'),
+        'IncomeList' => array('Type' => 'TIncomeList', 'Caption' => 'Příjmy', 'Default' => '0'),
+        'SpendingList' => array('Type' => 'TSpendingList', 'Caption' => 'Výdaje', 'Default' => '0'),
+        'TaxIncomeList' => array('Type' => 'TTaxIncomeList', 'Caption' => 'Daňové příjmy', 'Default' => '0'),
+        'TaxSpendingList' => array('Type' => 'TTaxSpendingList', 'Caption' => 'Daňové výdaje', 'Default' => '0'),
+        'ClaimsList' => array('Type' => 'TClaimsList', 'Caption' => 'Pohledávky', 'Default' => '0'),
+        'LiabilitiesList' => array('Type' => 'TLiabilitiesList', 'Caption' => 'Závazky', 'Default' => '0'),
+      ),
+      //'AfterInsert' => array($this, 'AfterInsertFinanceYear'),
+    ));
     $this->System->FormManager->RegisterFormType('TClaimsList', array(
       'Type' => 'ManyToOne',