<?php
include('../style.php');
include('include.php');
include('bills.php');

ShowHeader('Přepočet financí', 'Přepočet financí');

function RecalculateUsersFinance()
{
  global $InternetSegmentId, $Tarify, $Sprava, $Database, $UserIdNetwork; 

  echo("Aktualizuju finance uživatelů...<br>\n");
  $DbResult = $Database->query("SELECT * FROM users WHERE role=2 OR id=".$UserIdNetwork);  // Select network members only
  while($Row = $DbResult->fetch_array())
  {
    $DbResult2 = $Database->query("SELECT SUM(money) FROM finance_operations WHERE user=".$Row['id']);  // Account state 
    $Row3 = $DbResult2->fetch_array();
    $DbResult2 = $Database->query("SELECT SUM(consumption) FROM network_devices WHERE user=".$Row['id']." AND used=1");
    $Row4 = $DbResult2->fetch_array();

    $Price = 0;
    $Consumption = 0;
    $ID = $Row['network_segment'];
    while($ID != 0)
    {
      $DbResult2 = $Database->query("SELECT * FROM network_segments WHERE id=".$ID);
      $Row2 = $DbResult2->fetch_array();
      if(!(($Row['inet_hw'] == 0) and ($ID == $InternetSegmentId)))
      {
        $Price += $Row2['price'] / $Row2['users'];
        $Consumption += $Row2['consumption'] / $Row2['users_overheads'];
      }
      //echo($ID.' '.$InternetSegment.' '.$InternetSegmentId.' '.$Row['inet_hw'].' '.$Price.'<br>');
      $ID = $Row2['parent'];
    }
    $Monthly = 0;
    $MonthlyInet = $Tarify[$Row['inet_tarif_next']]['price'];
    if($Row['inet'] == 1) $Monthly += $MonthlyInet; 
    //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>');
    $Database->update('users', 'id='.$Row['id'], array('network_device' => $Price, 'monthly_inet' => $MonthlyInet, 'monthly' => $Monthly, 'consumption' => W2Kc($Consumption), 'cash' => $Row3[0], 'plus' => W2Kc($Row4[0])));
  }
}

function RecalculateSegmentParameters()
{
  global $Database, $InternetSegmentId;

  echo("Aktualizuju parametry segmentů...<br>\n");
  $Database->query("UPDATE network_segments SET users = 0, users_overheads = 0");  		// Vynulovat počty uživatelů
  $DbResult = $Database->query("SELECT * FROM network_segments");
  while($Row = $DbResult->fetch_array())
  {
    //echo('Segment '.$Row['name'].'<br>');
    $DbResult2 = $Database->query("SELECT users FROM network_segments WHERE id=".$Row['id']);
    $RowP = $DbResult2->fetch_array();
    $DbResult2 = $Database->query("SELECT users_overheads FROM network_segments WHERE id=".$Row['id']);
    $RowP2 = $DbResult2->fetch_array();

    $DbResult2 = $Database->query("SELECT SUM(price) as Price, SUM(consumption) as Consumption FROM network_devices WHERE segment=".$Row['id']." AND used=1");
    $Row2 = $DbResult2->fetch_array();
    $DbResult2 = $Database->query("SELECT COUNT(*) FROM users WHERE network_segment=".$Row['id'].' AND hw=1');
    $Row3 = $DbResult2->fetch_array();
    $DbResult2 = $Database->query("SELECT COUNT(*) FROM users WHERE network_segment=".$Row['id'].' AND overheads=1');
    $Row5 = $DbResult2->fetch_array();
    $ID = $Row['parent'];
    while($ID != 0)
    {
      //echo($ID.', ');
      $DbResult2 = $Database->query("SELECT * FROM network_segments WHERE id=".$ID);
      $Row4 = $DbResult2->fetch_array();
      $Database->update('network_segments', 'id='.$Row4['id'], array('users' => ($Row4['users'] + $Row3[0]), 'users_overheads' => ($Row4['users_overheads'] + $Row5[0])));
      $ID = $Row4['parent'];
    }
    //echo('Pocet '.$Row3[0].','.$Row['hosts'].'<br>');
    $Database->update('network_segments', 'id='.$Row['id'], array('price' => $Row2['Price'], 'users' => ($Row3[0] + $RowP['users']), 'consumption' => $Row2['Consumption'], 'users_overheads' => ($Row5[0] + $RowP2['users_overheads'])));
  }

  // Zkorigovat segment Internet
  $DbResult = $Database->select('users', 'COUNT(*)', 'inet_hw=1');
  $Row = $DbResult->fetch_array();
  $DbResult = $Database->select('users', 'COUNT(*)', 'inet=1');
  $Row2 = $DbResult->fetch_array();
  $DbResult = $Database->update('network_segments','id='.$InternetSegmentId, array('users' => $Row[0], 'users_overheads' => $Row2[0]));
}

function CreateMonthlyOverallBill()
{
  global $Database, $Sprava, $SpravaUsers, $TotalConsumption, $UserIdNetwork, $Internet, $TotalInternetPaid;

  $TotalConsumptionPrice = W2Kc($TotalConsumption);
 
  echo("Spotřeba energie: ".-$TotalConsumptionPrice."<br>\n");
  echo("Správa sítě: ".-($Sprava * $SpravaUsers)."<br>\n");
  echo("Internet: ".-$Internet."<br>\n");
  echo("Poplatky učástníků: ".$TotalInternetPaid."<br>\n");

  $Database->insert('finance_operations', array('money' => -$TotalConsumptionPrice, 'user' => $UserIdNetwork, 'type' => 2, 'date' => 'NOW()', 'comment' => 'Spotřeba energie', 'group' => 2));
  $Database->insert('finance_operations', array('money' => -($Sprava * $SpravaUsers), 'user' => $UserIdNetwork, 'type' => 2, 'date' => 'NOW()', 'comment' => 'Správa sítě', 'group' => 2));
  $Database->insert('finance_operations', array('money' => -$Internet, 'user' => $UserIdNetwork, 'type' => 2, 'date' => 'NOW()', 'comment' => 'Internet', 'group' => 2));
  $Database->insert('finance_operations', array('money' => $TotalInternetPaid, 'user' => $UserIdNetwork, 'type' => 2, 'date' => 'NOW()', 'comment' => 'Poplatky učástníků', 'group' => 2));
}

function DiscountNetworkDevicePrice()
{
  global $Database;
  $DbResult = $Database->query("SELECT *,CONCAT(second_name, ' ', first_name) as fullname FROM users WHERE role=2");
  while($Row = $DbResult->fetch_array())
  {
    echo($Row['fullname'].': '.($Row['network_device'])."<br>\n");
    $Database->insert('finance_operations', array('money' => (-$Row['network_device']), 'user' => $Row['id'], 'type' => 2, 'date' => 'NOW()', 'comment' => 'Jednorázový poplatek za společné zařízení', 'group' => 1));
    //echo($Database->LastQuery."<br>\n");
  }
}

// Načti poslední měsíční přehled a nastavení
$DbResult = $Database->select('finance_monthly_overall', '*', '1 ORDER BY date DESC LIMIT 1');
$Overall = $DbResult->fetch_array();

echo('Datum: '.date('j.n.Y')."<br>");
$Mesice = array('', 'Leden', 'Únor', 'Březen', 'Duben', 'Květen', 'Červen', 'Červenec', 'Srpen', 'Září', 'Říjen', 'Listopad', 'Prosinec');
$Datum = explode('-', $Overall['date']);
$Mesic = date("m") + 0;

RecalculateSegmentParameters();
RecalculateTariffs();
//DiscountNetworkDevicePrice();
//$InvoiceGenerator->CustomGenerate();
RecalculateUsersFinance();

// Celkovy prehled
$DbResult = $Database->query("SELECT SUM(price) FROM network_devices WHERE used=1");
$Row4 = $DbResult->fetch_array();
echo("Celková cena zařízení: Zařízení(".$Row4[0]);
$DbResult = $Database->query("SELECT SUM(price) FROM network_segments");
$Row = $DbResult->fetch_array();
echo("), Segmenty(".$Row[0]);
$DbResult = $Database->query("SELECT SUM(network_device) FROM users");
$Row5 = $DbResult->fetch_array();
echo("), Uživatelé(".$Row5[0].")<br>\n");
$DbResult = $Database->query("SELECT SUM(cash) FROM users WHERE role=2");
$Row6 = $DbResult->fetch_array();
echo("Stav pokladny: Uživatelé(".$Row6[0].")");
$DbResult = $Database->query("SELECT SUM(money) FROM finance_operations WHERE `group`=1"); // Položky uživatelů
$Row2 = $DbResult->fetch_array();
echo(", Operace-Zařízení(".($Row2[0] - $Row4[0]).")<br>\n");
echo("Rozdíl peněz v operacích: ".($Row2[0] - $Row6[0] - $Row4[0])." <br>\n");


$DbResult = $Database->query("SELECT SUM(consumption) FROM network_devices WHERE used=1");
$Row = $DbResult->fetch_array();
$CelkemSpotreba = W2Kc($Row[0]);

$SpravaCelkem = $Sprava * $SpravaUsers;
echo("Kontrola placení (Zaplaceno-Sprava-Internet): ".$TotalPaid."-".$SpravaCelkem."-".$Internet."=".($TotalPaid - $SpravaCelkem - $Internet)."<br>\n\n");

// Zkontrolovat odečtení měsíčního poplatku
//$Mesic = '1';
echo('Kontrola odečtení poplatků: Poslední měsíc-'.round($Datum[1]).' Aktuální měsíc-'.$Mesic."<br>\n");
if($Mesic != $Datum[1])
{
  echo("Odečítám měsíční poplatek...<br>\n");

  // Generuj účetní položky
  $DbResult = $Database->query("SELECT *,CONCAT(second_name, ' ', first_name) as fullname FROM users WHERE role=2");
  while($Row = $DbResult->fetch_array())
  {
    echo($Row['fullname'].': '.($Row['plus'] - $Row['monthly'])."<br>\n");

    $BillId = $InvoiceGenerator->CreateBill($Row['id'], array(array('description' => 'Poplatek za připojení k síti', 'price' => ($Row['monthly'] - $Row['plus']), 'quantity' => 1)), time(), time() + (date("t", time()) - 1) * 24 * 3600);

    $Database->insert('finance_operations',array('money' => ($Row['plus'] - $Row['monthly']), 'user' => $Row['id'], 'type' => 2, 'date' => 'NOW()', 'comment' => 'Poplatek za měsíc '.$Mesice[$Mesic], 'group' => 1, 'bill_id' => $BillId));
  }

  echo('Měním aktuální parametry sítě...<br>\n');

  // Update finance charge
  $Database->delete('finance_charge', 'period=0');
  $DbResult = $Database->select('finance_charge', '*', 'period=1');
  $Charge = $DbResult->fetch_array();
  //print_r($Charge);
  $DbResult = $Database->insert('finance_charge', array('period' => 0, 'internet' => $Charge['internet'], 
    'internet_speed' => $Charge['internet_speed'], 'internet_speed_reserve' => $Charge['internet_speed_reserve'], 'administration_per_user' => $Charge['administration_per_user'], 'kWh' => $Charge['kWh'], 'base_speed_element' => $Charge['base_speed_element'], 'BaseTariffPrice' => $Charge['BaseTariffPrice'], 'TopTariffPrice' => $Charge['TopTariffPrice']));
  
  echo("Přidávám měsíční přehled...<br>\n");

  $Database->insert('finance_monthly_overall', array('date' => 'NOW()', 'money' => $Internet, 'kWh' => $kWh, 'administration' => $Sprava, 'administration_total' => $SpravaCelkem, 'consumption_total' => $CelkemSpotreba, 'total_paid' => $TotalPaid, 'BaseTariffPrice' => $Charge['BaseTariffPrice'], 'TopTariffPrice' => $Charge['TopTariffPrice']));
  
  echo("Měním aktuální tarify....<br>\n");

  // Update tarrifs
  $Database->delete('finance_tariffs', 'period=0');
  $DbResult = $Database->select('finance_tariffs', '*', 'period=1');
  while($Tariff = $DbResult->fetch_array())
  {
    $Database->insert('finance_tariffs', array('period' => 0, 'name' => $Tariff['name'], 'id' => $Tariff['id'],
      'speed_factor' => $Tariff['speed_factor'], 'price_units' => $Tariff['price_units'], 'group_id' => $Tariff['group_id'], 'min_speed' => $Tariff['min_speed'], 'max_speed' => $Tariff['max_speed'], 'price' => $Tariff['price']));
  }
  
  echo("Měním aktuální tarify uživatelů...<br>\n");

  // Update tariff user selection
  $DbResult = $Database->query("SELECT id,inet_tarif_next FROM users WHERE role=2");
  while($User = $DbResult->fetch_array())
  {
    $Database->update('users', 'id='.$User['id'], array('inet_tarif_now' => $User['inet_tarif_next']));
  }

  RecalculateTariffs();
  RecalculateUsersFinance();
  CreateMonthlyOverallBill();
  RecalculateUsersFinance();
  
  // Restart traffic shaping
  $Database->update('services_restart', 'id=3', array('changed' => 1));
}

ShowFooter();

?>