Changeset 720 for trunk/Modules/Finance/Manage.php

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.

File:

• trunk/Modules/Finance/Manage.php (modified) (9 diffs)

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())