source: trunk/finance/finance.php@ 275

<?php

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 = 71;
  var $BillingPeriods;
  
  function LoadTariffs($Period = 1)
  {
    $this->Tariffs = array();
    $DbResult = $this->Database->select('FinanceTariff', '*', 'Period='.$Period.' ORDER BY Tariff, SpeedMax');
    while($Tariff = $DbResult->fetch_array())
    {
      $Tariff['SpeedMin'] = $Tariff['SpeedMin'] * 1024;
      $Tariff['SpeedMax'] = $Tariff['SpeedMax'] * 1024;
      $this->Tariffs[$Tariff['Tariff']] = $Tariff;
    }  
  }
  
  function RecalculateTariffs($Period = 1)
  {
    $ResidualSpeed = $this->MaxSpeed * 1024;
  
    $this->LoadTariffs($Period);
    
    $Column = array('Current', 'Next');
    $TotalMemberCount = 0;
    $TotalMaxSpeed = 0;
    foreach($this->Tariffs as $Index => $Tariff)
    {
      $DbResult = $this->Database->select('Member', 'COUNT(*)', 'InternetTariff'.$Column[$Period].'Month='.$Index);
      $Row = $DbResult->fetch_row();
      $this->Tariffs[$Index]['MemberCount'] = $Row[0];
      $Tariffs['MemberCount'] = $Row[0];
      
      //echo($Tariff['Name'].' '.$Tariff['MemberCount'].' '.$Tariff['SpeedMax'] * $Tariff['MemberCount'].' '.$ResidualSpeed.'<br />');
      switch($Tariff['Group'])
      {
        case 1:
          $TotalMemberCount += $Tariff['MemberCount'];
          $TotalMaxSpeed += $Tariff['SpeedMax'] * $Tariff['MemberCount'];
          break;
        case 2:
          $ResidualSpeed -= $Tariff['SpeedMin'] * $Tariff['MemberCount'];
          break;
        case 3:
          break;
      }
    }
    $Aggregation = $ResidualSpeed / $TotalMaxSpeed;
    //echo($TotalMaxSpeed.' '.$Aggregation.'<br />');
  
    // Recalculate price
    foreach($this->Tariffs as $Index => $Tariff)
    {
      switch($Tariff['Group'])
      {
        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['SpeedMin'] = round($Tariff['SpeedMax'] * $Aggregation);
          break;
        case 2:
          break;
        case 3:
          break;
      }
      //echo('MinSpeed: '.$Tariff['SpeedMin'].'<br />');
      $this->Database->update('FinanceTariff', 'Tariff='.$Tariff['Tariff'], array('SpeedMin' => ($Tariff['SpeedMin'] / 1024), 'MemberCount' => $Tariff['MemberCount']));
    }
    $this->LoadTariffs($Period);
  }

  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;
    
    $DbResult = $this->Database->query('SELECT * FROM finance_charge WHERE period='.$Period);
    $Row = $DbResult->fetch_array();
    $this->kWh = $Row['kWh'];
    $this->Internet = $Row['internet'];
    $this->Sprava = $Row['administration_per_user'];
    $this->RealMaxSpeed = $Row['internet_speed'];
    $this->SpeedReserve = $Row['internet_speed_reserve'];
    $this->BaseSpeedElement = $Row['base_speed_element'];
    $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];
    
    $this->SpravaUsers = $this->InternetUsers;
    $DbResult = $this->Database->query('SELECT SUM(consumption) FROM network_segments');
    $TotalConsumption = $DbResult->fetch_array();
    $this->TotalConsumption = $TotalConsumption[0];

    $DbResult = $this->Database->query('SELECT SUM(`MonthlyInternet`) AS `MonthlyInternet`, SUM(`MonthlyTotal`) AS `MonthlyTotal` FROM MemberPayment');
    $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 Shortcut FROM DocumentLineSequence WHERE DocumentLine='.Id.' AND FinanceYear'.$FinanceYear['Id']);
    $Sequence = $DbResult->fetch_assoc();
    
    if($Sequence['YearPrefix'] == 1)
    {
      $Result = $DbRow['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 FinanceClaimsLiabilities WHERE Subject='.$Member['Subject'].')) as Cash');
      $Cash = $DbResult2->fetch_row();
      $Cash = $Cash[0];
    
      $DbResult2 = $this->Database->query('SELECT SUM(consumption) FROM network_devices WHERE (user='.$Member['Id'].') AND (used = 1)');
      $ConsumptionPlus = $DbResult2->fetch_row();
      $ConsumptionPlus = $ConsumptionPlus[0];

      $NetworkDevice = 0;
      $Consumption = 0;
      $ID = $Member['NetworkSegment'];
      while($ID != 0)
      {
        $DbResult2 = $this->Database->query('SELECT * FROM network_segments WHERE id='.$ID);
        $Device = $DbResult2->fetch_assoc();
        $NetworkDevice += $Device['price'] / $Device['users'];
        $Consumption += $Device['consumption'] / $Device['users_overheads'];
        //echo($ID.' '.$InternetSegment.' '.$InternetSegmentId.' '.$Row['inet_hw'].' '.$Price.'<br>');
        $ID = $Device['parent'];
      }
    
      $Monthly = 0;
      $MonthlyInet = $this->Tariffs[$Member['InternetTariffNextMonth']]['Price'];
      //if($Row['inet'] == 1)
      $Monthly += $MonthlyInet; 
      $Monthly -= $this->W2Kc($ConsumptionPlus); 
      $Monthly -= $Member['Hire'];
      //if($Row['overheads'] == 1) $Monthly += $Sprava; // + W2Kc($Consumption); 
      //echo($Row['fullname'].' '.$Row['inet'].' '.$Monthly.'<br>');
      $Monthly = round($Monthly);
      //echo($Row['fullname'].' '.$Row['inet'].' '.$Monthly.'<br>');
      
      $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->Modules['Log']->NewRecord('Finance', 'RecalculateMemberPayment');  
    $this->RecalculateTariffs(1);
    $this->RecalculateTariffs(0);
    return($Output);
  } 
  
  function RecalculateSegmentParameters()
  {
    $Output = 'Aktualizuji parametry segmentů...<br />';
    $this->Database->query('UPDATE network_segments SET users = 0, users_overheads = 0');     // Vynulovat počty uživatelů
    $DbResult = $this->Database->query('SELECT * FROM network_segments');
    while($NetworkSegment = $DbResult->fetch_array())
    {
      //echo('Segment '.$Row['name'].'<br>');
      $DbResult2 = $this->Database->query('SELECT users FROM network_segments WHERE id='.$NetworkSegment['id']);
      $RowP = $DbResult2->fetch_array();
      $DbResult2 = $this->Database->query('SELECT users_overheads FROM network_segments WHERE id='.$NetworkSegment['id']);
      $RowP2 = $DbResult2->fetch_array();

      $DbResult2 = $this->Database->query('SELECT SUM(price) as Price, SUM(consumption) as Consumption FROM network_devices WHERE segment='.$NetworkSegment['id'].' AND used=1');
      $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)
      {
        //echo($ID.', ');
        $DbResult2 = $this->Database->query('SELECT * FROM network_segments WHERE id='.$ID);
        $Row4 = $DbResult2->fetch_array();
        $this->Database->update('network_segments', 'id='.$Row4['id'], array('users' => ($Row4['users'] + $Row3[0]), 'users_overheads' => ($Row4['users_overheads'] + $Row3[0])));
        $ID = $Row4['parent'];
      }
      $this->Database->update('network_segments', 'id='.$NetworkSegment['id'], array('price' => $Row2['Price'], 'users' => ($Row3[0] + $RowP['users']), 'consumption' => $Row2['Consumption'], 'users_overheads' => ($Row3[0] + $RowP2['users_overheads'])));
    }

    // Zkorigovat segment Internet
    $DbResult = $this->Database->select('Member', 'COUNT(*)');
    $Row = $DbResult->fetch_array();
    $DbResult = $this->Database->update('network_segments','id='.$this->InternetSegmentId, array('users' => $Row[0], 'users_overheads' => $Row[0]));
    $this->System->Modules['Log']->NewRecord('Finance', 'RecalculateSegmentParameters');
    return($Output);
  }  
}

?>