Ignore:
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:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/Modules/Finance/Finance.php

    r524 r537  
    1313include_once(dirname(__FILE__).'/Zivnost.php');
    1414include_once(dirname(__FILE__).'/finance.php');
     15
     16define('TARIFF_FREE', 7);
     17define('INVOICE_DUE_DAYS', 15);
     18define('INVOICE_OUT_DOC_LINE', 6);
     19
     20class Finance extends Module
     21{
     22  var $kWh;
     23  var $Internet;
     24  var $Sprava;
     25  var $DatumOdecteni;
     26  var $InternetUsers;
     27  var $SpravaUsers;
     28  var $InternetSegmentId = 21;
     29  var $MaxSpeed;
     30  var $RealMaxSpeed;
     31  var $SpeedReserve;
     32  var $BaseSpeedElement;
     33  var $TotalConsumption;
     34  var $UserIdNetwork = 46;
     35  var $BaseTariffPrice;
     36  var $TopTariffPrice;
     37  var $TotalPaid;
     38  var $TotalInternetPaid;
     39  var $Tariffs;
     40  var $ExternalSubject = 96;
     41  var $MainSubject;
     42  var $BillingPeriods;
     43  var $DirectoryId;
     44
     45  function LoadTariffs()
     46  {
     47    $this->Tariffs = array();
     48    $DbResult = $this->Database->select('Service', '*', '`ReplaceId` IS NULL ORDER BY `Name`, `InternetSpeedMax`');
     49    while($Tariff = $DbResult->fetch_array())
     50    {
     51      $Tariff['InternetSpeedMin'] = $Tariff['InternetSpeedMin'] * 1000;
     52      $Tariff['InternetSpeedMax'] = $Tariff['InternetSpeedMax'] * 1000;
     53      $this->Tariffs[$Tariff['Id']] = $Tariff;
     54    }
     55  }
     56
     57  function RecalculateTariffs()
     58  {
     59    $ResidualSpeed = $this->MaxSpeed * 1000;
     60
     61    $this->LoadTariffs();
     62
     63    $Column = array('Current', 'Next');
     64    $TotalMemberCount = 0;
     65    $TotalMaxSpeed = 0;
     66    foreach($this->Tariffs as $Index => $Tariff)
     67    {
     68      $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Member` '.
     69          'LEFT JOIN `ServiceCustomerRel` ON `ServiceCustomerRel`.`Customer`=`Member`.`Id` '.
     70          'WHERE (`ServiceCustomerRel`.`Service`='.$Index.') AND (`Member`.`BillingPeriod` > 1) '.
     71          'AND (`Member`.`Blocked`=0)');
     72      $Row = $DbResult->fetch_row();
     73      $this->Tariffs[$Index]['CustomerCount'] = $Row[0];
     74      $Tariffs['CustomerCount'] = $Row[0];
     75
     76      switch($Tariff['Category'])
     77      {
     78        case 1:
     79          $TotalMemberCount += $Tariff['CustomerCount'];
     80          $TotalMaxSpeed += $Tariff['InternetSpeedMax'] * $Tariff['CustomerCount'];
     81          break;
     82        case 2:
     83          $ResidualSpeed -= $Tariff['InternetSpeedMin'] * $Tariff['CustomerCount'];
     84          break;
     85        case 3:
     86          break;
     87      }
     88    }
     89    if($TotalMaxSpeed > 0) $Aggregation = $ResidualSpeed / $TotalMaxSpeed;
     90    else $Aggregation = 1;
     91
     92    // Recalculate price
     93    foreach($this->Tariffs as $Index => $Tariff)
     94    {
     95      switch($Tariff['Category'])
     96      {
     97        case 1:
     98          // Přepočítávání rychlostí koliduje s rozdílovým zapisováním stromu front do mikrotiku.
     99          // 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.
     100          //$Tariff['InternetSpeedMin'] = round($Tariff['InternetSpeedMax'] * $Aggregation);
     101          break;
     102        case 2:
     103          break;
     104        case 3:
     105          break;
     106      }
     107      $this->Database->update('Service', 'Id='.$Tariff['Id'],
     108          array('InternetSpeedMin' => ($Tariff['InternetSpeedMin'] / 1000),
     109              'CustomerCount' => $Tariff['CustomerCount']));
     110    }
     111    $this->LoadTariffs();
     112  }
     113
     114  function LoadMonthParameters($Period = 1) // 0 - now, 1 - next month
     115  {
     116    $DbResult = $this->Database->query('SELECT * FROM `FinanceBillingPeriod`');
     117    while($BillingPeriod = $DbResult->fetch_assoc())
     118      $this->BillingPeriods[$BillingPeriod['Id']] = $BillingPeriod;
     119
     120    // Period parameter is not used as it have to be determined from item replacement
     121    $DbResult = $this->Database->query('SELECT * FROM `FinanceCharge` WHERE `ReplaceId` IS NULL LIMIT 1');
     122    $Row = $DbResult->fetch_array();
     123    $this->kWh = $Row['kWh'];
     124    $this->Internet = $Row['Internet'];
     125    $this->Sprava = $Row['AdministrationPerUser'];
     126    $this->RealMaxSpeed = $Row['InternetSpeed'];
     127    $this->SpeedReserve = $Row['InternetSpeedReserve'];
     128    $this->BaseSpeedElement = $Row['BaseSpeedElement'];
     129    $this->MaxSpeed = $this->RealMaxSpeed - $this->SpeedReserve;
     130    $this->TopTariffPrice = $Row['TopTariffPrice'];
     131    $this->BaseTariffPrice = $Row['BaseTariffPrice'];
     132
     133    $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Member`');
     134    $Row = $DbResult->fetch_row();
     135    $this->InternetUsers = $Row[0];
     136    $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Member` WHERE (`Blocked`=0) AND (`BillingPeriod` > 1)');
     137    $Row = $DbResult->fetch_row();
     138    $this->PayingUsers = $Row[0];
     139
     140    $this->SpravaUsers = $this->PayingUsers;
     141
     142    $DbResult = $this->Database->query('SELECT SUM(`Consumption`) FROM `NetworkSegment`');
     143    $TotalConsumption = $DbResult->fetch_array();
     144    $this->TotalConsumption = $TotalConsumption[0];
     145
     146    $DbResult = $this->Database->query('SELECT SUM(`MemberPayment`.`MonthlyInternet`) AS `MonthlyInternet`, '.
     147        'SUM(`MemberPayment`.`MonthlyTotal`) AS `MonthlyTotal` '.
     148        'FROM `MemberPayment` JOIN `Member` ON `Member`.`Id`=`MemberPayment`.`Member` WHERE `Member`.`Blocked`=0');
     149    $Row = $DbResult->fetch_assoc();
     150    $this->TotalInternetPaid = $Row['MonthlyInternet'];
     151    $this->TotalPaid = $Row['MonthlyTotal'];
     152
     153    $this->LoadTariffs($Period);
     154  }
     155
     156  function W2Kc($Spotreba)
     157  {
     158    return(round($Spotreba * 0.72 * $this->kWh));
     159  }
     160
     161  function GetNextDocumentLineNumber($Id, $FinanceYear = 0)
     162  {
     163    if($FinanceYear == 0)
     164    {
     165      // Get latest year
     166      $DbResult = $this->Database->select('FinanceYear', '*', '1 ORDER BY `Year` DESC LIMIT 1');
     167    } else $DbResult = $this->Database->select('FinanceYear', '*', 'Id='.$FinanceYear);
     168    $FinanceYear = $DbResult->fetch_assoc();
     169
     170    $DbResult = $this->Database->query('SELECT `Shortcut`, `Id` FROM `DocumentLine` WHERE `Id`='.$Id);
     171    $DocumentLine = $DbResult->fetch_assoc();
     172
     173    $DbResult = $this->Database->query('SELECT * FROM `DocumentLineSequence` WHERE '.
     174        '`DocumentLine`='.$Id.' AND `FinanceYear`='.$FinanceYear['Id']);
     175    $Sequence = $DbResult->fetch_assoc();
     176
     177    if($Sequence['YearPrefix'] == 1)
     178    {
     179      $Result = $DocumentLine['Shortcut'].$Sequence['NextNumber'].'/'.$FinanceYear['Year'];
     180    } else $Result = $DocumentLine['Shortcut'].$Sequence['NextNumber'];
     181
     182    $this->Database->query('UPDATE `DocumentLineSequence` SET `NextNumber` = `NextNumber` + 1 '.
     183        'WHERE `DocumentLine`='.$Id.' AND `FinanceYear`='.$FinanceYear['Id']);
     184    return($Result);
     185  }
     186
     187  function RecalculateMemberPayment()
     188  {
     189    $Output = 'Aktualizuji finance členů...<br />';
     190    $this->Database->query('TRUNCATE TABLE `MemberPayment`');
     191    $DbResult = $this->Database->query('SELECT * FROM `Member`');
     192    while($Member = $DbResult->fetch_assoc())
     193    {
     194      $DbResult2 = $this->Database->query('SELECT ((SELECT COALESCE(SUM(Value), 0) FROM FinanceOperation '.
     195          'WHERE Subject='.$Member['Subject'].') + (SELECT COALESCE(SUM(-Value), 0) FROM FinanceInvoice '.
     196          'WHERE Subject='.$Member['Subject'].')) as Cash');
     197      $Cash = $DbResult2->fetch_row();
     198      $Cash = $Cash[0];
     199
     200      $DbResult2 = $this->Database->query('SELECT SUM(`Product`.`Consumption`) * `StockItem`.`Amount` '.
     201          'FROM `StockItem` JOIN `Product` ON `Product`.`Id` = `StockItem`.`Product` '.
     202          'WHERE (`StockItem`.`Location` = '.$Member['Id'].') AND (`StockItem`.`TimeElimination` IS NULL)');
     203      $ConsumptionPlus = $DbResult2->fetch_row();
     204      $ConsumptionPlus = $ConsumptionPlus[0];
     205
     206      $NetworkDevice = 0;
     207      $Consumption = 0;
     208      $Id = $Member['NetworkSegment'];
     209      while(($Id != '') and ($Id != 0))
     210      {
     211        $DbResult2 = $this->Database->query('SELECT * FROM `NetworkSegment` WHERE `Id`='.$Id);
     212        $Device = $DbResult2->fetch_assoc();
     213        if($Device['Users'] > 0) $NetworkDevice += $Device['Price'] / $Device['Users'];
     214        if($Device['UsersOverheads'] > 0) $Consumption += $Device['Consumption'] / $Device['UsersOverheads'];
     215        $Id = $Device['Parent'];
     216      }
     217
     218      $DbResult2 = $this->Database->query('SELECT SUM(`Service`.`Price`) AS `Price` '.
     219          'FROM `ServiceCustomerRel` LEFT JOIN '.
     220          '`Service` ON `Service`.`Id` = `ServiceCustomerRel`.`Service` WHERE `ServiceCustomerRel`.`Customer`='.
     221          $Member['Id'].' AND `ServiceCustomerRel`.`Action` IS NULL');
     222      $DbRow = $DbResult2->fetch_assoc();
     223      $Monthly = 0;
     224      if($DbRow['Price'] != '') $MonthlyInet = $DbRow['Price'];
     225      else $MonthlyInet = 0;
     226
     227      $Monthly += $MonthlyInet;
     228      $Monthly -= $this->W2Kc($ConsumptionPlus);
     229      $Monthly = round($Monthly);
     230
     231      if($Member['BillingPeriodNext'] == 1)
     232      {
     233        // Inactive payer
     234        $MonthlyInet = 0;
     235        $Monthly = 0;
     236        $ConsumptionPlus = 0;
     237        $Consumption = 0;
     238      }
     239      $this->Database->insert('MemberPayment', array('Member' => $Member['Id'],
     240          'NetworkDevice' => $NetworkDevice, 'MonthlyInternet' => $MonthlyInet,
     241          'MonthlyTotal' => $Monthly, 'MonthlyConsumption' => $this->W2Kc($Consumption),
     242          'Cash' => $Cash, 'MonthlyPlus' => $this->W2Kc($ConsumptionPlus)));
     243    }
     244    $this->System->ModuleManager->Modules['Log']->NewRecord('Finance', 'RecalculateMemberPayment');
     245    $this->RecalculateTariffs(1);
     246    $this->RecalculateTariffs(0);
     247    return($Output);
     248  }
     249
     250  function RecalculateSegmentParameters()
     251  {
     252    $Output = 'Aktualizuji parametry segmentů...<br />';
     253    $this->Database->query('UPDATE `NetworkSegment` SET `Users` = 0, `UsersOverheads` = 0');     // Vynulovat počty uživatelů
     254    $DbResult = $this->Database->query('SELECT * FROM `NetworkSegment`');
     255    while($NetworkSegment = $DbResult->fetch_array())
     256    {
     257      $DbResult2 = $this->Database->query('SELECT `Users` FROM `NetworkSegment` WHERE `Id`='.$NetworkSegment['Id']);
     258      $RowP = $DbResult2->fetch_array();
     259      $DbResult2 = $this->Database->query('SELECT `UsersOverheads` FROM `NetworkSegment` WHERE `Id`='.$NetworkSegment['Id']);
     260      $RowP2 = $DbResult2->fetch_array();
     261
     262      $DbResult2 = $this->Database->query('SELECT SUM(`Product`.`BuyPrice`) * `StockItem`.`Amount` AS `Price`, '.
     263          'SUM(`Product`.`Consumption`) * `StockItem`.`Amount` AS `Consumption` '.
     264          'FROM `StockItem` JOIN `Product` ON `Product`.`Id` = `StockItem`.`Product` '.
     265          'WHERE (`StockItem`.`Segment`='.$NetworkSegment['Id'].') AND (`StockItem`.`TimeElimination` IS NULL)');
     266      $Row2 = $DbResult2->fetch_array();
     267      $DbResult2 = $this->Database->query('SELECT COUNT(*) FROM Member WHERE NetworkSegment='.$NetworkSegment['Id']);
     268      $Row3 = $DbResult2->fetch_array();
     269      $ID = $NetworkSegment['Parent'];
     270      while($ID != 0)
     271      {
     272        $DbResult2 = $this->Database->query('SELECT * FROM NetworkSegment WHERE Id='.$ID);
     273        $Row4 = $DbResult2->fetch_array();
     274        $this->Database->update('NetworkSegment', 'Id='.$Row4['Id'],
     275            array('Users' => ($Row4['Users'] + $Row3[0]), 'UsersOverheads' => ($Row4['UsersOverheads'] + $Row3[0])));
     276        $ID = $Row4['Parent'];
     277      }
     278      $this->Database->update('NetworkSegment', 'Id='.$NetworkSegment['Id'],
     279          array('Price' => $Row2['Price'], 'Users' => ($Row3[0] + $RowP['Users']), 'Consumption' => $Row2['Consumption'], 'UsersOverheads' => ($Row3[0] + $RowP2['UsersOverheads'])));
     280    }
     281
     282    // Zkorigovat segment Internet
     283    $DbResult = $this->Database->select('Member', 'COUNT(*)');
     284    $Row = $DbResult->fetch_array();
     285    $DbResult = $this->Database->update('NetworkSegment', 'Id='.$this->InternetSegmentId,
     286        array('Users' => $Row[0], 'UsersOverheads' => $Row[0]));
     287    $this->System->ModuleManager->Modules['Log']->NewRecord('Finance', 'RecalculateSegmentParameters');
     288    return($Output);
     289  }
     290}
    15291
    16292class ModuleFinance extends AppModule
     
    55331    $this->System->Modules['Finance']->MainSubject = $Config['Finance']['MainSubjectId'];
    56332    $this->System->Modules['Finance']->DirectoryId = $Config['Finance']['DirectoryId'];
    57     $this->System->Modules['Finance']->LoadMonthParameters(0); 
    58333  } 
    59334 
Note: See TracChangeset for help on using the changeset viewer.