Changeset 726 for trunk/Modules/Finance/UserState.php

Timestamp:

Jan 5, 2015, 10:01:40 PM (10 years ago)

Author:

chronos

Message:

• Modified: Added time designation for scheduled table change actions. This applies to tables Service, ServiceCustomerRel and FinanceCharge. Now time of taking effect is not determined just by customer pay period but by more generally by specified time.

File:

• trunk/Modules/Finance/UserState.php (modified) (7 diffs)

trunk/Modules/Finance/UserState.php

@@ -9 +9 @@
   function ShowFinanceOperation($Subject)
   {
-        $UserOperationTableQuery = '((SELECT Text, Time, (Value*Direction) AS Value, File, BillCode, NULL AS PeriodFrom, NULL AS PeriodTo '.
-          'FROM FinanceOperation WHERE (Subject='.$Subject['Id'].')) UNION ALL '.
-                '(SELECT (SELECT GROUP_CONCAT(Description SEPARATOR ",") FROM FinanceInvoiceItem WHERE FinanceInvoice=FinanceInvoice.Id) AS Text, '.
-                'Time, -(Value*Direction) as Value, File, BillCode, PeriodFrom, PeriodTo FROM FinanceInvoice WHERE (Subject='.$Subject['Id'].')))';
+        $UserOperationTableQuery = '((SELECT `Text`, `Time`, (`Value`*`Direction`) AS `Value`, `File`, `BillCode`, NULL AS `PeriodFrom`, NULL AS `PeriodTo` '.
+          'FROM `FinanceOperation` WHERE (`Subject`='.$Subject['Id'].')) UNION ALL '.
+                '(SELECT (SELECT GROUP_CONCAT(`Description` SEPARATOR ",") FROM `FinanceInvoiceItem` WHERE `FinanceInvoice`=`FinanceInvoice`.`Id`) AS `Text`, '.
+                '`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>';
-        $DbResult = $this->Database->query('SELECT COUNT(*) FROM '.$UserOperationTableQuery.' AS T1');
+        $DbResult = $this->Database->query('SELECT COUNT(*) FROM '.$UserOperationTableQuery.' AS `T1`');
         $DbRow = $DbResult->fetch_row();
         $PageList = GetPageList($DbRow[0]);
@@ -33 +33 @@
         $Output .= $Order['Output'];
 
-        $StateQuery = 'SELECT SUM(T2.Value) FROM '.$UserOperationTableQuery.
-          ' AS T2 WHERE T2.Time <= T1.Time ';
-        $Query = 'SELECT *, ('.$StateQuery.') AS State FROM '.$UserOperationTableQuery.' AS T1 '.$Order['SQL'].$PageList['SQLLimit'];
+        $StateQuery = 'SELECT SUM(`T2`.`Value`) FROM '.$UserOperationTableQuery.
+          ' AS `T2` WHERE `T2`.`Time` <= `T1`.`Time` ';
+        $Query = 'SELECT *, ('.$StateQuery.') AS `State` FROM '.$UserOperationTableQuery.' AS `T1` '.$Order['SQL'].$PageList['SQLLimit'];
 
         $DbResult = $this->Database->query($Query);
@@ -78 +78 @@
       if(!$this->System->User->CheckPermission('Finance', 'DisplaySubjectState')) return('Nemáte oprávnění');
       $UserId = $this->System->User->User['Id'];
-      $DbResult = $this->Database->query('SELECT Customer FROM UserCustomerRel WHERE User='.$UserId.' LIMIT 1');
+      $DbResult = $this->Database->query('SELECT `Customer` FROM `UserCustomerRel` WHERE `User`='.$UserId.' LIMIT 1');
       if($DbResult->num_rows > 0)
       {
@@ -87 +87 @@
         
     // Load customer info
-    $DbResult = $this->Database->query('SELECT * FROM Member WHERE Id='.$CustomerId);
+    $DbResult = $this->Database->query('SELECT * FROM `Member` WHERE `Id`='.$CustomerId);
     if($DbResult->num_rows == 1)
     {
@@ -95 +95 @@
     
     // Load subject info
-    $DbResult = $this->Database->query('SELECT * FROM Subject WHERE Id='.$Customer['Subject']);
+    $DbResult = $this->Database->query('SELECT * FROM `Subject` WHERE `Id`='.$Customer['Subject']);
     if($DbResult->num_rows == 1)
     {    
@@ -105 +105 @@
 
     // Account state
-    $UserOperationTableQuery = '((SELECT Text, Time, (Value*Direction) AS Value, File, BillCode, NULL AS PeriodFrom, NULL AS PeriodTo '.
-      'FROM FinanceOperation WHERE (Subject='.$Subject['Id'].')) UNION ALL '.
-      '(SELECT (SELECT GROUP_CONCAT(Description SEPARATOR ",") FROM FinanceInvoiceItem WHERE FinanceInvoice=FinanceInvoice.Id) AS Text, '.
-      '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);
+    $UserOperationTableQuery = '((SELECT `Text`, `Time`, (`Value`*`Direction`) AS `Value`, `File`, `BillCode`, NULL AS `PeriodFrom`, NULL AS `PeriodTo` '.
+      'FROM `FinanceOperation` WHERE (`Subject`='.$Subject['Id'].')) UNION ALL '.
+      '(SELECT (SELECT GROUP_CONCAT(`Description` SEPARATOR ",") FROM `FinanceInvoiceItem` WHERE `FinanceInvoice`=`FinanceInvoice`.`Id`) AS `Text`, '.
+      '`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();
     $Total = $DbRow['Total'];
@@ -133 +133 @@
     $Output .= 'Rozpis měsíčního poplatku:<br><table class="WideTable">'.
       '<tr><th>Služba</th><th>Cena [Kč]</th></tr>';
-    $DbResult = $this->Database->query('SELECT Service.Name, Service.Price FROM ServiceCustomerRel '.
-      'LEFT JOIN Service ON Service.Id=ServiceCustomerRel.Service '.
-      'WHERE (ServiceCustomerRel.Customer='.$Customer['Id'].') AND (ServiceCustomerRel.Action IS NULL)');
+    $DbResult = $this->Database->query('SELECT `Service`.`Name`, `Service`.`Price` FROM `ServiceCustomerRel` '.
+      'LEFT JOIN `Service` ON `Service`.`Id`=`ServiceCustomerRel`.`Service` '.
+      'WHERE (`ServiceCustomerRel`.`Customer`='.$Customer['Id'].') AND (`ServiceCustomerRel`.`ChangeAction` IS NULL)');
     while($DbRow = $DbResult->fetch_assoc())
     {