Changeset 185


Ignore:
Timestamp:
Apr 3, 2009, 10:32:35 AM (15 years ago)
Author:
george
Message:
  • Opraveno: Přepočítávání stavu financí členů a generování periodických plateb.
Location:
trunk
Files:
1 added
1 deleted
4 edited

Legend:

Unmodified
Added
Removed

  • trunk/Readme.txt

    r182 r185  
    2727  aktuality/uploads - vlastníka nastavit na apache
    2828 
     295) Generování PDF dokumentů
     30
     31  Nutno nainstalovat balík htmldoc. Na Fedoře pomocí yum install htmldoc.

  • trunk/finance/finance.php

    r171 r185  
    2323  var $ExternalSubject = 96;
    2424  var $MainSubject = 71;
     25        var $BillingPeriods;
    2526 
    2627  function RecalculateTariffs($Period = 1)
    … …  
    9899  function LoadMonthParameters($Period = 1) // 0 - now, 1 - next month
    99100  {   
    100     $DbResult = $this->Database->query("SELECT * FROM finance_charge WHERE period=".$Period);
     101                $DbResult = $this->Database->query('SELECT * FROM FinanceBillingPeriod');
     102                while($BillingPeriod = $DbResult->fetch_assoc())
     103                  $this->BillingPeriods[$BillingPeriod['Id']] = $BillingPeriod;
     104               
     105    $DbResult = $this->Database->query('SELECT * FROM finance_charge WHERE period='.$Period);
    101106    $Row = $DbResult->fetch_array();
    102107    $this->kWh = $Row['kWh'];
    … …  
    140145    return($Result);
    141146  }
     147 
     148  function RecalculateMemberPayment()
     149  {
     150    $Output = 'Aktualizuju finance členů...<br />';
     151    $this->Database->query('TRUNCATE TABLE MemberPayment');
     152    $DbResult = $this->Database->query('SELECT * FROM Member');
     153    while($Member = $DbResult->fetch_assoc())
     154    {
     155      $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');
     156      $Cash = $DbResult2->fetch_row();
     157      $Cash = $Cash[0];
     158         
     159      $DbResult2 = $this->Database->query('SELECT SUM(consumption) FROM network_devices WHERE (user='.$Member['Id'].') AND (used = 1)');
     160      $ConsumptionPlus = $DbResult2->fetch_row();
     161      $ConsumptionPlus = $ConsumptionPlus[0];
     162
     163      $NetworkDevice = 0;
     164      $Consumption = 0;
     165      $ID = $Member['NetworkSegment'];
     166      while($ID != 0)
     167      {
     168        $DbResult2 = $this->Database->query('SELECT * FROM network_segments WHERE id='.$ID);
     169        $Device = $DbResult2->fetch_assoc();
     170        $NetworkDevice += $Device['price'] / $Device['users'];
     171        $Consumption += $Device['consumption'] / $Device['users_overheads'];
     172        //echo($ID.' '.$InternetSegment.' '.$InternetSegmentId.' '.$Row['inet_hw'].' '.$Price.'<br>');
     173        $ID = $Device['parent'];
     174      }
     175         
     176      $Monthly = 0;
     177      $MonthlyInet = $this->Tarify[$Member['InternetTariffNextMonth']]['price'];
     178      //if($Row['inet'] == 1)
     179      $Monthly += $MonthlyInet;
     180      $Monthly -= $this->W2Kc($ConsumptionPlus);
     181      //if($Row['overheads'] == 1) $Monthly += $Sprava; // + W2Kc($Consumption);
     182      //echo($Row['fullname'].' '.$Row['inet'].' '.$Monthly.'<br>');
     183      $Monthly = round($Monthly);
     184      //echo($Row['fullname'].' '.$Row['inet'].' '.$Monthly.'<br>');
     185               
     186      $this->Database->insert('MemberPayment', array('Member' => $Member['Id'], 'NetworkDevice' => $NetworkDevice, 'MonthlyInternet' => $MonthlyInet, 'MonthlyTotal' => $Monthly, 'MonthlyConsumption' => $this->W2Kc($Consumption), 'Cash' => $Cash, 'MonthlyPlus' => $this->W2Kc($ConsumptionPlus)));
     187    }
     188                $this->System->Modules['Log']->NewRecord('Finance', 'RecalculateMemberPayment');       
     189    return($Output);
     190  }
     191 
     192  function RecalculateSegmentParameters()
     193  {
     194    $Output = 'Aktualizuju parametry segmentů...<br />';
     195    $this->Database->query('UPDATE network_segments SET users = 0, users_overheads = 0');               // Vynulovat počty uživatelů
     196    $DbResult = $this->Database->query('SELECT * FROM network_segments');
     197    while($NetworkSegment = $DbResult->fetch_array())
     198    {
     199      //echo('Segment '.$Row['name'].'<br>');
     200      $DbResult2 = $this->Database->query('SELECT users FROM network_segments WHERE id='.$NetworkSegment['id']);
     201      $RowP = $DbResult2->fetch_array();
     202      $DbResult2 = $this->Database->query('SELECT users_overheads FROM network_segments WHERE id='.$NetworkSegment['id']);
     203      $RowP2 = $DbResult2->fetch_array();
     204
     205      $DbResult2 = $this->Database->query('SELECT SUM(price) as Price, SUM(consumption) as Consumption FROM network_devices WHERE segment='.$NetworkSegment['id'].' AND used=1');
     206      $Row2 = $DbResult2->fetch_array();
     207      $DbResult2 = $this->Database->query('SELECT COUNT(*) FROM Member WHERE NetworkSegment='.$NetworkSegment['id']);
     208      $Row3 = $DbResult2->fetch_array();
     209      $ID = $NetworkSegment['parent'];
     210      while($ID != 0)
     211      {
     212        //echo($ID.', ');
     213        $DbResult2 = $this->Database->query('SELECT * FROM network_segments WHERE id='.$ID);
     214        $Row4 = $DbResult2->fetch_array();
     215        $this->Database->update('network_segments', 'id='.$Row4['id'], array('users' => ($Row4['users'] + $Row3[0]), 'users_overheads' => ($Row4['users_overheads'] + $Row3[0])));
     216        $ID = $Row4['parent'];
     217      }
     218      //echo('Pocet '.$Row3[0].','.$Row['hosts'].'<br>');
     219      $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'])));
     220    }
     221
     222    // Zkorigovat segment Internet
     223    $DbResult = $this->Database->select('Member', 'COUNT(*)');
     224    $Row = $DbResult->fetch_array();
     225    $DbResult = $this->Database->update('network_segments','id='.$this->InternetSegmentId, array('users' => $Row[0], 'users_overheads' => $Row[0]));
     226                $this->System->Modules['Log']->NewRecord('Finance', 'RecalculateSegmentParameters');
     227    return($Output);
     228  } 
    142229}
    143230

  • trunk/finance/manage.php

    r182 r185  
    4848        $Output = $this->ShowNewInvoiceForm();
    4949        break;
     50      case 'Recalculate':
     51        $Output = $this->System->Modules['Finance']->RecalculateSegmentParameters();
     52        $Output .= $this->System->Modules['Finance']->RecalculateMemberPayment();
     53        break;
     54      case 'MonthlyPayment':
     55        $Output = $this->ShowMonthlyPayment();
     56        break;
    5057      default:
    5158        $Output = '<a href="?Operation=NewDeviceForm">Přidat nové zařízení</a><br />';
    … …  
    5360        $Output .= '<a href="?Operation=NewPaymentForm">Přidat novou platbu</a><br />';
    5461        $Output .= '<a href="?Operation=NewInvoiceForm">Přidat novou fakturu</a><br />';
    55         $Output .= '<a href="?Operation=ImportOldData">Importovat stará data</a><br />';
     62        //$Output .= '<a href="?Operation=ImportOldData">Importovat stará data</a><br />';
    5663        $Output .= '<a href="?Operation=ConvertPDFToFile">Převést data z databáze do souborů</a><br />';
    5764        $Output .= '<a href="?Operation=Bills">Správa dokladů</a><br />';
    5865        //$Output .= '<a href="?Operation=ConvertData">Převést tabulky</a><br />';
    59                     $Output .= '<a href="prepocet.php">Přepočet financí</a><br />';
    60                     $Output .= '<a href="clenove.php">Seznam členů</a><br />';
    61                     $Output .= '<a href="zivnost.php">Živnost</a><br />';
     66        $Output .= '<a href="?Operation=Recalculate">Přepočet financí</a><br />';
     67        $Output .= '<a href="?Operation=MonthlyPayment">Měsíční vyúčtování</a><br />';
     68        $Output .= '<a href="clenove.php">Seznam členů</a><br />';
     69        $Output .= '<a href="zivnost.php">Živnost</a><br />';
    6270    }
    6371    return($Output);
    … …  
    229237  }
    230238
     239  /*
    231240  function ImportOldData()
    232241  {
    233     die();
    234    
    235242    $Output = '';
    236243    $this->Database->query('TRUNCATE TABLE FinanceCashFlow');
    … …  
    355362    return($Output);
    356363  }
    357 
     364  */
     365 
    358366  function ConvertPDFDataToFiles()
    359367  {
    … …  
    378386  }
    379387
     388  /*
    380389  function ConvertData()
    381390  {
    382     die();
    383    
    384391    $Finance = $this->System->Modules['Finance'];
    385392          $Output = '';
    … …  
    440447        return($Output);
    441448  }
     449  */
     450
     451       
     452        function GetBillingPeriod($Period)
     453        {
     454    $MonthCount = $this->System->Modules['Finance']->BillingPeriods[$Period]['MonthCount'];
     455    $PeriodFrom = mktime(0, 0, 0, date('n'), 1, date('Y'));
     456    $PeriodTo = mktime(0, 0, 0, date('n') + $MonthCount - 1, date('t', mktime(0, 0, 0, date('n') + $MonthCount - 1, 1, date('Y'))) , date('Y'));
     457                return(array('From' => $PeriodFrom, 'To' => $PeriodTo, 'MonthCount' => $MonthCount));
     458        }
     459
     460  function ShowMonthlyPayment()
     461  {
     462    if(!$this->System->Modules['User']->CheckPermission('Finance', 'Manage')) return('Nemáte oprávnění');
     463          $Output = '';
     464
     465    $Finance = &$this->System->Modules['Finance'];
     466         
     467    // Načti poslední měsíční přehled a nastavení
     468    $DbResult = $this->Database->select('finance_monthly_overall', '*', '1 ORDER BY date DESC LIMIT 1');
     469    $Overall = $DbResult->fetch_array();
     470
     471    $Output -= 'Datum: '.date('j.n.Y').'<br />';
     472   
     473    $Datum = explode('-', $Overall['date']);
     474    $Mesic = date('m') + 0;
     475
     476    $Output .= $Finance->RecalculateSegmentParameters();
     477    $Output .= $Finance->RecalculateTariffs();
     478    //$InvoiceGenerator->CustomGenerate();
     479    $Output .= $Finance->RecalculateMemberPayment();
     480
     481    // Celkovy prehled
     482    $DbResult = $this->Database->query('SELECT SUM(price) FROM network_devices WHERE used = 1');
     483    $Row = $DbResult->fetch_row();
     484                $TotalDeviceCost = $Row[0];
     485    $Output .= 'Celková cena zařízení: Zařízení('.$TotalDeviceCost;
     486               
     487    $DbResult = $this->Database->query('SELECT SUM(price) FROM network_segments');
     488    $Row = $DbResult->fetch_row();
     489                $TotalSegmentDeviceCost = $Row[0];
     490    $Output .= '), Segmenty('.$TotalSegmentDeviceCost;
     491               
     492    $DbResult = $this->Database->query('SELECT SUM(NetworkDevice) FROM MemberPayment');
     493    $Row = $DbResult->fetch_row();
     494                $TotalMemberDeviceCost = $Row[0];
     495    $Output .= '), Členové('.$TotalMemberDeviceCost.')<br />';
     496               
     497    $DbResult = $this->Database->query('SELECT SUM(Cash) FROM MemberPayment');
     498    $Row = $DbResult->fetch_row();
     499                $TotalMemberCash = $Row[0];
     500    $Output .= 'Stav pokladny: Členové('.$TotalMemberCash.')';   
     501
     502    $DbResult = $this->Database->query('SELECT SUM(consumption) FROM network_devices WHERE used=1');
     503    $Row = $DbResult->fetch_row();
     504                $TotalConsumption = $Row[0];
     505    $TotalConsumptionCost = $Finance->W2Kc($TotalConsumption);
     506
     507    $SpravaCelkem = $Finance->Sprava * $Finance->SpravaUsers;
     508    $Output .= 'Kontrola placení (Zaplaceno-Sprava-Internet): '.$Finance->TotalPaid.'-'.$SpravaCelkem.'-'.$Finance->Internet.'='.($Finance->TotalPaid - $SpravaCelkem - $Finance->Internet).'<br />';
     509
     510    // Zkontrolovat odečtení měsíčního poplatku
     511    //$Mesic = '1';
     512    $Output .= 'Kontrola odečtení poplatků: Poslední měsíc-'.round($Datum[1]).' Aktuální měsíc-'.$Mesic.'<br />';
     513    //if($Mesic != $Datum[1])
     514    {
     515      $Output .= 'Odečítám měsíční poplatek...<br />';
     516
     517      // Generuj účetní položky
     518      $DbResult = $this->Database->query('SELECT Member.*, MemberPayment.MonthlyTotal, UNIX_TIMESTAMP(Member.BillingPeriodLastDate), Subject.Name AS SubjectName FROM MemberPayment JOIN Member ON Member.Id=MemberPayment.Member JOIN Subject ON Subject.Id=Member.Subject');
     519      while($Member = $DbResult->fetch_array())
     520      {
     521        $Output .= $Member['SubjectName'].': ';
     522                                $Period = $this->GetBillingPeriod($Member['BillingPeriodNext']);
     523                                if($Period['From'] > $Member['UNIX_TIMESTAMP(Member.BillingPeriodLastDate)'])
     524                                {
     525                                        $this->Database->update('Member', 'Id='.$Member['Id'], array('BillingPeriod' => $Member['BillingPeriodNext'], 'InternetTariffCurrentMonth' => $Member['InternetTariffNextMonth']));
     526                                        $Member['BillingPeriod'] = $Member['BillingPeriodNext'];
     527                                }
     528                                $Period = $this->GetBillingPeriod($Member['BillingPeriod']);
     529                                $PayPerPeriod = $Member['MonthlyTotal'] * $Period['MonthCount'];
     530        if(($Period['From'] > $Member['UNIX_TIMESTAMP(Member.BillingPeriodLastDate)']) and ($Member['InternetTariffCurrentMonth'] != 6) and ($PayPerPeriod > 0))
     531        {
     532          //echo($Mesic.'%'.$MonthCount.'='.($Mesic % $MonthCount).' ');                                               
     533          $TimePeriodText = date('j.n.Y', $Period['From']).' - '.date('j.n.Y', $Period['To']);
     534          $Output .= $TimePeriodText.': '.$Member['MonthlyTotal'].' * '.$Period['MonthCount'].' = '.$PayPerPeriod.'<br />';
     535          $BillCode = $Finance->GetNextDocumentLineNumber(6); // Faktury vydané
     536          $BillId = $this->System->Modules['Bill']->CreateBill($Member['Subject'], array(array('Description' => 'Připojení k síti', 'Price' => $PayPerPeriod, 'Quantity' => 1)), $Period['From'], $Period['To'], $BillCode);
     537          $this->Database->insert('FinanceClaimsLiabilities', array('Value' => $PayPerPeriod, 'Subject' => $Member['Subject'], 'TimeCreation' => 'NOW()', 'Text' => 'Připojení k síti za období '.$TimePeriodText, 'Bill' => $BillId, 'BillCode' => $BillCode));
     538                                        $this->Database->update('Member', 'Id='.$Member['Id'], array('BillingPeriodLastDate' => TimeToMysqlDateTime($Period['To'])));
     539        } else $Output .= '<br />';
     540      }
     541                       
     542      // Update finance charge
     543      $Output .= 'Měním aktuální parametry sítě...<br>';
     544      $this->Database->delete('finance_charge', 'period = 0');
     545      $DbResult = $this->Database->select('finance_charge', '*', 'period = 1');
     546      $Charge = $DbResult->fetch_assoc();
     547      //print_r($Charge);
     548      $DbResult = $this->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']));
     549
     550      $Output .= 'Přidávám měsíční přehled...<br />';
     551      $this->Database->insert('finance_monthly_overall', array('date' => 'NOW()', 'money' => $Finance->Internet, 'kWh' => $Finance->kWh, 'administration' => $Finance->Sprava, 'administration_total' => $SpravaCelkem, 'consumption_total' => $TotalConsumptionCost, 'total_paid' => $Finance->TotalPaid, 'BaseTariffPrice' => $Charge['BaseTariffPrice'], 'TopTariffPrice' => $Charge['TopTariffPrice'], 'member_count' => $Finance->InternetUsers));
     552
     553      $Output .= 'Měním aktuální tarify....<br>';
     554      // Update tarrifs
     555      $this->Database->delete('finance_tariffs', 'period=0');
     556      $DbResult = $this->Database->select('finance_tariffs', '*', 'period = 1');
     557      while($Tariff = $DbResult->fetch_array())
     558      {
     559        $this->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']));
     560      }
     561
     562      $Output .= $Finance->RecalculateTariffs();
     563            $Finance->RecalculateMemberPayment();
     564      //CreateMonthlyOverallBill();
     565      //$Finance->RecalculateUsersFinance();
     566
     567      // Restart traffic shaping
     568      $this->Database->update('services_restart', 'id = 3', array('changed' => 1));
     569    }
     570    return($Output);
     571  }
    442572}
    443573

  • trunk/log.php

    r148 r185  
    77  function NewRecord($Module, $Operation, $Value = '')
    88  {
    9     $this->Database->insert('is`.`Log', array('Time' => 'NOW()', 'User' => $this->System->Modules['User']->User['Id'], 'Module' => $Module, 'Operation' => $Operation, 'Value' => $Value));
     9    $this->Database->insert('Log', array('Time' => 'NOW()', 'User' => $this->System->Modules['User']->User['Id'], 'Module' => $Module, 'Operation' => $Operation, 'Value' => $Value));
    1010    //echo($this->Database->LastQuery);
    1111  }
Note: See TracChangeset for help on using the changeset viewer.