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

Timestamp:

May 18, 2013, 7:03:01 PM (11 years ago)

Author:

chronos

Message:

• Upraveno: Neinicializovat finance při generování jakékoliv stránky, ale jen těch, které používají modul Finance.
• Upraveno: Soubor finance.php byl odstraněn a obsah přesunut do Finance.php. Na systémech bez rozlišování velikosti písmen jména soubor nelze mít dva soubory o stejném jméně.

File:

• trunk/Modules/Finance/Finance.php (modified) (2 diffs)

trunk/Modules/Finance/Finance.php

@@ -13 +13 @@
 include_once(dirname(__FILE__).'/Zivnost.php');
 include_once(dirname(__FILE__).'/finance.php');
+
+define('TARIFF_FREE', 7);
+define('INVOICE_DUE_DAYS', 15);
+define('INVOICE_OUT_DOC_LINE', 6);
+
+class Finance extends Module
+{
+  var $kWh;
+  var $Internet;
+  var $Sprava;
+  var $DatumOdecteni;
+  var $InternetUsers;
+  var $SpravaUsers;
+  var $InternetSegmentId = 21;
+  var $MaxSpeed;
+  var $RealMaxSpeed;
+  var $SpeedReserve;
+  var $BaseSpeedElement;
+  var $TotalConsumption;
+  var $UserIdNetwork = 46;
+  var $BaseTariffPrice;
+  var $TopTariffPrice;
+  var $TotalPaid;
+  var $TotalInternetPaid;
+  var $Tariffs;
+  var $ExternalSubject = 96;
+  var $MainSubject;
+  var $BillingPeriods;
+  var $DirectoryId;
+
+  function LoadTariffs()
+  {
+    $this->Tariffs = array();
+    $DbResult = $this->Database->select('Service', '*', '`ReplaceId` IS NULL ORDER BY `Name`, `InternetSpeedMax`');
+    while($Tariff = $DbResult->fetch_array())
+    {
+      $Tariff['InternetSpeedMin'] = $Tariff['InternetSpeedMin'] * 1000;
+      $Tariff['InternetSpeedMax'] = $Tariff['InternetSpeedMax'] * 1000;
+      $this->Tariffs[$Tariff['Id']] = $Tariff;
+    }
+  }
+
+  function RecalculateTariffs()
+  {
+    $ResidualSpeed = $this->MaxSpeed * 1000;
+
+    $this->LoadTariffs();
+
+    $Column = array('Current', 'Next');
+    $TotalMemberCount = 0;
+    $TotalMaxSpeed = 0;
+    foreach($this->Tariffs as $Index => $Tariff)
+    {
+      $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Member` '.
+          'LEFT JOIN `ServiceCustomerRel` ON `ServiceCustomerRel`.`Customer`=`Member`.`Id` '.
+          'WHERE (`ServiceCustomerRel`.`Service`='.$Index.') AND (`Member`.`BillingPeriod` > 1) '.
+          'AND (`Member`.`Blocked`=0)');
+      $Row = $DbResult->fetch_row();
+      $this->Tariffs[$Index]['CustomerCount'] = $Row[0];
+      $Tariffs['CustomerCount'] = $Row[0];
+
+      switch($Tariff['Category'])
+      {
+        case 1:
+          $TotalMemberCount += $Tariff['CustomerCount'];
+          $TotalMaxSpeed += $Tariff['InternetSpeedMax'] * $Tariff['CustomerCount'];
+          break;
+        case 2:
+          $ResidualSpeed -= $Tariff['InternetSpeedMin'] * $Tariff['CustomerCount'];
+          break;
+        case 3:
+          break;
+      }
+    }
+    if($TotalMaxSpeed > 0) $Aggregation = $ResidualSpeed / $TotalMaxSpeed;
+    else $Aggregation = 1;
+
+    // Recalculate price
+    foreach($this->Tariffs as $Index => $Tariff)
+    {
+      switch($Tariff['Category'])
+      {
+        case 1:
+          // Přepočítávání rychlostí koliduje s rozdílovým zapisováním stromu front do mikrotiku.
+          // Vždy při změně počtu počítačů či domácností docházelo ke změně minima a přepočtu všeho.
+          //$Tariff['InternetSpeedMin'] = round($Tariff['InternetSpeedMax'] * $Aggregation);
+          break;
+        case 2:
+          break;
+        case 3:
+          break;
+      }
+      $this->Database->update('Service', 'Id='.$Tariff['Id'],
+          array('InternetSpeedMin' => ($Tariff['InternetSpeedMin'] / 1000),
+              'CustomerCount' => $Tariff['CustomerCount']));
+    }
+    $this->LoadTariffs();
+  }
+
+  function LoadMonthParameters($Period = 1) // 0 - now, 1 - next month
+  {
+    $DbResult = $this->Database->query('SELECT * FROM `FinanceBillingPeriod`');
+    while($BillingPeriod = $DbResult->fetch_assoc())
+      $this->BillingPeriods[$BillingPeriod['Id']] = $BillingPeriod;
+
+    // Period parameter is not used as it have to be determined from item replacement
+    $DbResult = $this->Database->query('SELECT * FROM `FinanceCharge` WHERE `ReplaceId` IS NULL LIMIT 1');
+    $Row = $DbResult->fetch_array();
+    $this->kWh = $Row['kWh'];
+    $this->Internet = $Row['Internet'];
+    $this->Sprava = $Row['AdministrationPerUser'];
+    $this->RealMaxSpeed = $Row['InternetSpeed'];
+    $this->SpeedReserve = $Row['InternetSpeedReserve'];
+    $this->BaseSpeedElement = $Row['BaseSpeedElement'];
+    $this->MaxSpeed = $this->RealMaxSpeed - $this->SpeedReserve;
+    $this->TopTariffPrice = $Row['TopTariffPrice'];
+    $this->BaseTariffPrice = $Row['BaseTariffPrice'];
+
+    $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Member`');
+    $Row = $DbResult->fetch_row();
+    $this->InternetUsers = $Row[0];
+    $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Member` WHERE (`Blocked`=0) AND (`BillingPeriod` > 1)');
+    $Row = $DbResult->fetch_row();
+    $this->PayingUsers = $Row[0];
+
+    $this->SpravaUsers = $this->PayingUsers;
+
+    $DbResult = $this->Database->query('SELECT SUM(`Consumption`) FROM `NetworkSegment`');
+    $TotalConsumption = $DbResult->fetch_array();
+    $this->TotalConsumption = $TotalConsumption[0];
+
+    $DbResult = $this->Database->query('SELECT SUM(`MemberPayment`.`MonthlyInternet`) AS `MonthlyInternet`, '.
+        'SUM(`MemberPayment`.`MonthlyTotal`) AS `MonthlyTotal` '.
+        'FROM `MemberPayment` JOIN `Member` ON `Member`.`Id`=`MemberPayment`.`Member` WHERE `Member`.`Blocked`=0');
+    $Row = $DbResult->fetch_assoc();
+    $this->TotalInternetPaid = $Row['MonthlyInternet'];
+    $this->TotalPaid = $Row['MonthlyTotal'];
+
+    $this->LoadTariffs($Period);
+  }
+
+  function W2Kc($Spotreba)
+  {
+    return(round($Spotreba * 0.72 * $this->kWh));
+  }
+
+  function GetNextDocumentLineNumber($Id, $FinanceYear = 0)
+  {
+    if($FinanceYear == 0)
+    {
+      // Get latest year
+      $DbResult = $this->Database->select('FinanceYear', '*', '1 ORDER BY `Year` DESC LIMIT 1');
+    } else $DbResult = $this->Database->select('FinanceYear', '*', 'Id='.$FinanceYear);
+    $FinanceYear = $DbResult->fetch_assoc();
+
+    $DbResult = $this->Database->query('SELECT `Shortcut`, `Id` FROM `DocumentLine` WHERE `Id`='.$Id);
+    $DocumentLine = $DbResult->fetch_assoc();
+
+    $DbResult = $this->Database->query('SELECT * FROM `DocumentLineSequence` WHERE '.
+        '`DocumentLine`='.$Id.' AND `FinanceYear`='.$FinanceYear['Id']);
+    $Sequence = $DbResult->fetch_assoc();
+
+    if($Sequence['YearPrefix'] == 1)
+    {
+      $Result = $DocumentLine['Shortcut'].$Sequence['NextNumber'].'/'.$FinanceYear['Year'];
+    } else $Result = $DocumentLine['Shortcut'].$Sequence['NextNumber'];
+
+    $this->Database->query('UPDATE `DocumentLineSequence` SET `NextNumber` = `NextNumber` + 1 '.
+        'WHERE `DocumentLine`='.$Id.' AND `FinanceYear`='.$FinanceYear['Id']);
+    return($Result);
+  }
+
+  function RecalculateMemberPayment()
+  {
+    $Output = 'Aktualizuji finance členů...<br />';
+    $this->Database->query('TRUNCATE TABLE `MemberPayment`');
+    $DbResult = $this->Database->query('SELECT * FROM `Member`');
+    while($Member = $DbResult->fetch_assoc())
+    {
+      $DbResult2 = $this->Database->query('SELECT ((SELECT COALESCE(SUM(Value), 0) FROM FinanceOperation '.
+          'WHERE Subject='.$Member['Subject'].') + (SELECT COALESCE(SUM(-Value), 0) FROM FinanceInvoice '.
+          'WHERE Subject='.$Member['Subject'].')) as Cash');
+      $Cash = $DbResult2->fetch_row();
+      $Cash = $Cash[0];
+
+      $DbResult2 = $this->Database->query('SELECT SUM(`Product`.`Consumption`) * `StockItem`.`Amount` '.
+          'FROM `StockItem` JOIN `Product` ON `Product`.`Id` = `StockItem`.`Product` '.
+          'WHERE (`StockItem`.`Location` = '.$Member['Id'].') AND (`StockItem`.`TimeElimination` IS NULL)');
+      $ConsumptionPlus = $DbResult2->fetch_row();
+      $ConsumptionPlus = $ConsumptionPlus[0];
+
+      $NetworkDevice = 0;
+      $Consumption = 0;
+      $Id = $Member['NetworkSegment'];
+      while(($Id != '') and ($Id != 0))
+      {
+        $DbResult2 = $this->Database->query('SELECT * FROM `NetworkSegment` WHERE `Id`='.$Id);
+        $Device = $DbResult2->fetch_assoc();
+        if($Device['Users'] > 0) $NetworkDevice += $Device['Price'] / $Device['Users'];
+        if($Device['UsersOverheads'] > 0) $Consumption += $Device['Consumption'] / $Device['UsersOverheads'];
+        $Id = $Device['Parent'];
+      }
+
+      $DbResult2 = $this->Database->query('SELECT SUM(`Service`.`Price`) AS `Price` '.
+          'FROM `ServiceCustomerRel` LEFT JOIN '.
+          '`Service` ON `Service`.`Id` = `ServiceCustomerRel`.`Service` WHERE `ServiceCustomerRel`.`Customer`='.
+          $Member['Id'].' AND `ServiceCustomerRel`.`Action` IS NULL');
+      $DbRow = $DbResult2->fetch_assoc();
+      $Monthly = 0;
+      if($DbRow['Price'] != '') $MonthlyInet = $DbRow['Price'];
+      else $MonthlyInet = 0;
+
+      $Monthly += $MonthlyInet;
+      $Monthly -= $this->W2Kc($ConsumptionPlus);
+      $Monthly = round($Monthly);
+
+      if($Member['BillingPeriodNext'] == 1)
+      {
+        // Inactive payer
+        $MonthlyInet = 0;
+        $Monthly = 0;
+        $ConsumptionPlus = 0;
+        $Consumption = 0;
+      }
+      $this->Database->insert('MemberPayment', array('Member' => $Member['Id'],
+          'NetworkDevice' => $NetworkDevice, 'MonthlyInternet' => $MonthlyInet,
+          'MonthlyTotal' => $Monthly, 'MonthlyConsumption' => $this->W2Kc($Consumption),
+          'Cash' => $Cash, 'MonthlyPlus' => $this->W2Kc($ConsumptionPlus)));
+    }
+    $this->System->ModuleManager->Modules['Log']->NewRecord('Finance', 'RecalculateMemberPayment');
+    $this->RecalculateTariffs(1);
+    $this->RecalculateTariffs(0);
+    return($Output);
+  }
+
+  function RecalculateSegmentParameters()
+  {
+    $Output = 'Aktualizuji parametry segmentů...<br />';
+    $this->Database->query('UPDATE `NetworkSegment` SET `Users` = 0, `UsersOverheads` = 0');     // Vynulovat počty uživatelů
+    $DbResult = $this->Database->query('SELECT * FROM `NetworkSegment`');
+    while($NetworkSegment = $DbResult->fetch_array())
+    {
+      $DbResult2 = $this->Database->query('SELECT `Users` FROM `NetworkSegment` WHERE `Id`='.$NetworkSegment['Id']);
+      $RowP = $DbResult2->fetch_array();
+      $DbResult2 = $this->Database->query('SELECT `UsersOverheads` FROM `NetworkSegment` WHERE `Id`='.$NetworkSegment['Id']);
+      $RowP2 = $DbResult2->fetch_array();
+
+      $DbResult2 = $this->Database->query('SELECT SUM(`Product`.`BuyPrice`) * `StockItem`.`Amount` AS `Price`, '.
+          'SUM(`Product`.`Consumption`) * `StockItem`.`Amount` AS `Consumption` '.
+          'FROM `StockItem` JOIN `Product` ON `Product`.`Id` = `StockItem`.`Product` '.
+          'WHERE (`StockItem`.`Segment`='.$NetworkSegment['Id'].') AND (`StockItem`.`TimeElimination` IS NULL)');
+      $Row2 = $DbResult2->fetch_array();
+      $DbResult2 = $this->Database->query('SELECT COUNT(*) FROM Member WHERE NetworkSegment='.$NetworkSegment['Id']);
+      $Row3 = $DbResult2->fetch_array();
+      $ID = $NetworkSegment['Parent'];
+      while($ID != 0)
+      {
+        $DbResult2 = $this->Database->query('SELECT * FROM NetworkSegment WHERE Id='.$ID);
+        $Row4 = $DbResult2->fetch_array();
+        $this->Database->update('NetworkSegment', 'Id='.$Row4['Id'],
+            array('Users' => ($Row4['Users'] + $Row3[0]), 'UsersOverheads' => ($Row4['UsersOverheads'] + $Row3[0])));
+        $ID = $Row4['Parent'];
+      }
+      $this->Database->update('NetworkSegment', 'Id='.$NetworkSegment['Id'],
+          array('Price' => $Row2['Price'], 'Users' => ($Row3[0] + $RowP['Users']), 'Consumption' => $Row2['Consumption'], 'UsersOverheads' => ($Row3[0] + $RowP2['UsersOverheads'])));
+    }
+
+    // Zkorigovat segment Internet
+    $DbResult = $this->Database->select('Member', 'COUNT(*)');
+    $Row = $DbResult->fetch_array();
+    $DbResult = $this->Database->update('NetworkSegment', 'Id='.$this->InternetSegmentId,
+        array('Users' => $Row[0], 'UsersOverheads' => $Row[0]));
+    $this->System->ModuleManager->Modules['Log']->NewRecord('Finance', 'RecalculateSegmentParameters');
+    return($Output);
+  }
+}
 
 class ModuleFinance extends AppModule
@@ -55 +331 @@
     $this->System->Modules['Finance']->MainSubject = $Config['Finance']['MainSubjectId'];
     $this->System->Modules['Finance']->DirectoryId = $Config['Finance']['DirectoryId'];
-    $this->System->Modules['Finance']->LoadMonthParameters(0);  
   }