Changeset 967

Timestamp:

Mar 6, 2024, 11:47:49 PM (8 weeks ago)

Author:

chronos

Message:

• Added: Monthly finance report.

Location:

trunk

Files:

• Application/UpdateTrace.php (modified) (3 diffs)
• Application/Version.php (modified) (1 diff)
• Common/Form/Types/String.php (modified) (1 diff)
• Modules/Finance/Finance.php (modified) (1 diff)

trunk/Application/UpdateTrace.php

@@ -619 +619 @@
   $Manager->Execute('ALTER TABLE `EmployeeSalary` ADD `Contract` INT NULL ,
 ADD INDEX ( `Contract` ) ;');
- $Manager->Execute('ALTER TABLE `EmployeeSalary` ADD FOREIGN KEY ( `Contract` ) REFERENCES `Contract` (
+  $Manager->Execute('ALTER TABLE `EmployeeSalary` ADD FOREIGN KEY ( `Contract` ) REFERENCES `Contract` (
 `Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;');
   $Manager->Execute("INSERT INTO `Action` (
@@ -2242 +2242 @@
 {
   $Manager->Execute('ALTER TABLE `Log` ADD `URL` VARCHAR(255) NOT NULL AFTER `IPAddress`;');
+}
+
+function UpdateTo967(UpdateManager $Manager): void
+{
+  $DbResult = $Manager->Execute('SELECT `Id` FROM `MenuItem` WHERE `Name`="Roční výkazy"');
+  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 , '', 'Měsíční přehled', '1', '/is/?t=FinanceMonthReport&a=list', NULL , NULL , NULL , '1'
+      );");
+    $ActionId = $Manager->Database->insert_id;
+    $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
+      "VALUES (NULL , 'Měsíční přehled', '".$DbRow['Id']."', '".$ActionId."', '1');");   
+  }
 }
 
@@ -2357 +2374 @@
       948 => array('Revision' => 953, 'Function' => 'UpdateTo953'),
       953 => array('Revision' => 957, 'Function' => 'UpdateTo957'),
+      957 => array('Revision' => 967, 'Function' => 'UpdateTo967'),
     );
   }

trunk/Application/Version.php

@@ -1 +1 @@
 <?php
 
-$Revision = 964; // Subversion revision
-$DatabaseRevision = 957; // SQL structure revision
-$ReleaseTime = strtotime('2024-02-03');
+$Revision = 967; // Subversion revision
+$DatabaseRevision = 967; // SQL structure revision
+$ReleaseTime = strtotime('2024-03-06');

trunk/Common/Form/Types/String.php

@@ -25 +25 @@
   function OnLoad(array $Item): ?string
   {
-    return $_POST[$Item['Name']];
+    if (array_key_exists($Item['Name'], $_POST))
+      return $_POST[$Item['Name']];
+      else return '';
   }
 

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',