Ignore:
Timestamp:
Feb 20, 2009, 9:53:27 AM (16 years ago)
Author:
george
Message:
  • Opraveno: Další vlna odstraňování odkazů na starou tabulku users.
  • Upraveno: Posílání konfigurace traffic shapingu do rotueru mikrotik po menších blocích.
  • Přidáno: Nová tabulka MemberPayment fungující jako bilance skutečných měsíčních plateb a jako cache některých výpočtů k placení.
  • Přidáno: Zobrazení běhových ladících informací na spodku stránky.
  • Opraveno: V XHTML hlavičce opravena volba kódování.
File:
1 edited

Legend:

Unmodified
Added
Removed

  • www/finance/prepocet.php

    r157 r163  
    1111    $Finance = &$this->System->Modules['Finance'];
    1212    $Output = 'Aktualizuju finance uživatelů...<br />';
    13     $DbResult = $this->Database->query('SELECT * FROM users WHERE role=2 OR id='.$Finance->UserIdNetwork);  // Select network members only
    14     while($Row = $DbResult->fetch_array())
    15     {
    16       $DbResult2 = $this->Database->query('SELECT SUM(money) FROM finance_operations WHERE user='.$Row['id']);  // Account state
    17       $Row3 = $DbResult2->fetch_array();
    18       $DbResult2 = $this->Database->query('SELECT SUM(consumption) FROM network_devices WHERE user='.$Row['id'].' AND used=1');
    19       $Row4 = $DbResult2->fetch_array();
    20 
    21       $Price = 0;
     13        $this->Database->query('TRUNCATE TABLE MemberPayment');
     14    $DbResult = $this->Database->query('SELECT * FROM Member');
     15    while($Member = $DbResult->fetch_assoc())
     16    {
     17      $DbResult2 = $this->Database->query('SELECT ((SELECT COALESCE(SUM(Value), 0) FROM FinanceCashFlow WHERE Subject='.$Member['Subject'].') + (SELECT COALESCE(SUM(-Value), 0) FROM FinanceClaimsLiabilities WHERE Subject='.$Member['Subject'].')) as Cash');
     18      $Cash = $DbResult2->fetch_row();
     19          $Cash = $Cash[0];
     20         
     21      $DbResult2 = $this->Database->query('SELECT SUM(consumption) FROM network_devices WHERE user='.$Member['Id'].' AND used=1');
     22      $ConsumptionPlus = $DbResult2->fetch_row();
     23          $ConsumptionPlus = $ConsumptionPlus[0];
     24
     25      $NetworkDevice = 0;
    2226      $Consumption = 0;
    23       $ID = $Row['network_segment'];
     27      $ID = $Member['NetworkSegment'];
    2428      while($ID != 0)
    2529      {
    2630        $DbResult2 = $this->Database->query('SELECT * FROM network_segments WHERE id='.$ID);
    27         $Row2 = $DbResult2->fetch_array();
    28         if(!(($Row['inet_hw'] == 0) and ($ID == $Finance->InternetSegmentId)))
    29         {
    30           $Price += $Row2['price'] / $Row2['users'];
    31           $Consumption += $Row2['consumption'] / $Row2['users_overheads'];
    32         }
     31        $Device = $DbResult2->fetch_assoc();
     32        $NetworkDevice += $Device['price'] / $Device['users'];
     33        $Consumption += $Device['consumption'] / $Device['users_overheads'];
    3334        //echo($ID.' '.$InternetSegment.' '.$InternetSegmentId.' '.$Row['inet_hw'].' '.$Price.'<br>');
    34         $ID = $Row2['parent'];
    35       }
     35        $ID = $Device['parent'];
     36      }
     37         
    3638      $Monthly = 0;
    37       $MonthlyInet = $Finance->Tarify[$Row['inet_tarif_next']]['price'];
    38       if($Row['inet'] == 1) $Monthly += $MonthlyInet;
     39      $MonthlyInet = $Finance->Tarify[$Member['InternetTariffNextMonth']]['price'];
     40      //if($Row['inet'] == 1)
     41          $Monthly += $MonthlyInet;
     42      $Monthly -= $ConsumptionPlus;
    3943      //if($Row['overheads'] == 1) $Monthly += $Sprava; // + W2Kc($Consumption);
    4044      //echo($Row['fullname'].' '.$Row['inet'].' '.$Monthly.'<br>');
    4145      $Monthly = round($Monthly);
    4246      //echo($Row['fullname'].' '.$Row['inet'].' '.$Monthly.'<br>');
    43       $this->Database->update('users', 'id='.$Row['id'], array('network_device' => $Price, 'monthly_inet' => $MonthlyInet, 'monthly' => $Monthly, 'consumption' => $this->System->Modules['Finance']->W2Kc($Consumption), 'cash' => $Row3[0], 'plus' => $Finance->W2Kc($Row4[0])));
     47               
     48      $this->Database->insert('MemberPayment', array('Member' => $Member['Id'], 'NetworkDevice' => $NetworkDevice, 'MonthlyInternet' => $MonthlyInet, 'MonthlyTotal' => $Monthly, 'MonthlyConsumption' => $this->System->Modules['Finance']->W2Kc($Consumption), 'Cash' => $Cash, 'MonthlyPlus' => $Finance->W2Kc($ConsumptionPlus)));
    4449    }
    4550        return($Output);
    … …  
    5257    $this->Database->query('UPDATE network_segments SET users = 0, users_overheads = 0');               // Vynulovat počty uživatelů
    5358    $DbResult = $this->Database->query('SELECT * FROM network_segments');
    54     while($Row = $DbResult->fetch_array())
     59    while($NetworkSegment = $DbResult->fetch_array())
    5560    {
    5661      //echo('Segment '.$Row['name'].'<br>');
    57       $DbResult2 = $this->Database->query('SELECT users FROM network_segments WHERE id='.$Row['id']);
     62      $DbResult2 = $this->Database->query('SELECT users FROM network_segments WHERE id='.$NetworkSegment['id']);
    5863      $RowP = $DbResult2->fetch_array();
    59       $DbResult2 = $this->Database->query("SELECT users_overheads FROM network_segments WHERE id=".$Row['id']);
     64      $DbResult2 = $this->Database->query('SELECT users_overheads FROM network_segments WHERE id='.$NetworkSegment['id']);
    6065      $RowP2 = $DbResult2->fetch_array();
    6166
    62       $DbResult2 = $this->Database->query("SELECT SUM(price) as Price, SUM(consumption) as Consumption FROM network_devices WHERE segment=".$Row['id']." AND used=1");
     67      $DbResult2 = $this->Database->query('SELECT SUM(price) as Price, SUM(consumption) as Consumption FROM network_devices WHERE segment='.$NetworkSegment['id'].' AND used=1');
    6368      $Row2 = $DbResult2->fetch_array();
    64       $DbResult2 = $this->Database->query("SELECT COUNT(*) FROM users WHERE network_segment=".$Row['id'].' AND hw=1');
     69      $DbResult2 = $this->Database->query('SELECT COUNT(*) FROM Member WHERE NetworkSegment='.$NetworkSegment['id']);
    6570      $Row3 = $DbResult2->fetch_array();
    66       $DbResult2 = $this->Database->query("SELECT COUNT(*) FROM users WHERE network_segment=".$Row['id'].' AND overheads=1');
     71      $DbResult2 = $this->Database->query('SELECT COUNT(*) FROM Member WHERE NetworkSegment='.$NetworkSegment['id']);
    6772      $Row5 = $DbResult2->fetch_array();
    68       $ID = $Row['parent'];
     73      $ID = $NetworkSegment['parent'];
    6974      while($ID != 0)
    7075      {
    7176        //echo($ID.', ');
    72         $DbResult2 = $this->Database->query("SELECT * FROM network_segments WHERE id=".$ID);
     77        $DbResult2 = $this->Database->query('SELECT * FROM network_segments WHERE id='.$ID);
    7378        $Row4 = $DbResult2->fetch_array();
    7479        $this->Database->update('network_segments', 'id='.$Row4['id'], array('users' => ($Row4['users'] + $Row3[0]), 'users_overheads' => ($Row4['users_overheads'] + $Row5[0])));
    … …  
    7681      }
    7782      //echo('Pocet '.$Row3[0].','.$Row['hosts'].'<br>');
    78       $this->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'])));
     83      $this->Database->update('network_segments', 'id='.$NetworkSegment['id'], array('price' => $Row2['Price'], 'users' => ($Row3[0] + $RowP['users']), 'consumption' => $Row2['Consumption'], 'users_overheads' => ($Row5[0] + $RowP2['users_overheads'])));
    7984    }
    8085
    8186    // Zkorigovat segment Internet
    82     $DbResult = $this->Database->select('users', 'COUNT(*)', 'inet_hw=1');
     87    $DbResult = $this->Database->select('Member', 'COUNT(*)');
    8388    $Row = $DbResult->fetch_array();
    84     $DbResult = $this->Database->select('users', 'COUNT(*)', 'inet=1');
    85     $Row2 = $DbResult->fetch_array();
    86     $DbResult = $this->Database->update('network_segments','id='.$Finance->InternetSegmentId, array('users' => $Row[0], 'users_overheads' => $Row2[0]));
     89    $DbResult = $this->Database->update('network_segments','id='.$Finance->InternetSegmentId, array('users' => $Row[0], 'users_overheads' => $Row[0]));
    8790    return($Output);
    8891  }
    8992
    90   function CreateMonthlyOverallBill()
     93  /*function CreateMonthlyOverallBill()
    9194  {
    9295    global $Sprava, $SpravaUsers, $TotalConsumption, $UserIdNetwork, $Internet, $TotalInternetPaid;
    … …  
    117120    }
    118121        return($Output);
    119   }
    120 
     122  }*/
    121123
    122124  function Show()
    … …  
    149151    $Row = $DbResult->fetch_array();
    150152    $Output .= "), Segmenty(".$Row[0];
    151     $DbResult = $this->Database->query("SELECT SUM(network_device) FROM users");
     153    $DbResult = $this->Database->query('SELECT SUM(NetworkDevice) FROM MemberPayment');
    152154    $Row5 = $DbResult->fetch_array();
    153155    $Output .= "), Uživatelé(".$Row5[0].")<br>\n";
    154     $DbResult = $this->Database->query("SELECT SUM(cash) FROM users WHERE role=2");
     156    $DbResult = $this->Database->query("SELECT SUM(Cash) FROM MemberPayment");
    155157    $Row6 = $DbResult->fetch_array();
    156158    $Output .= "Stav pokladny: Uživatelé(".$Row6[0].")";
    … …  
    178180
    179181      // Generuj účetní položky
    180       $DbResult = $this->Database->query("SELECT *, CONCAT(second_name, ' ', first_name) as fullname FROM users WHERE (role = 2)");
     182      $DbResult = $this->Database->query('SELECT * FROM MemberPayment JOIN Member ON Member.Id=MemberPayment.Member JOIN Subject ON Subject.Id=Member.Subject');
    181183      while($User = $DbResult->fetch_array())
    182184      {
    183         $Output .= $User['fullname'].': ';
     185        $Output .= $User['Name'].': ';
    184186        $MonthCount = $BillingPeriodMonthCount[$User['BillingPeriod']];
    185         if(($MonthCount > 0) and (((($Mesic - 1) % 12) % $MonthCount) == 0) and ($User['inet_tarif_now'] != 6))
     187        if(($MonthCount > 0) and (((($Mesic - 1) % 12) % $MonthCount) == 0) and ($User['InternetTariffCurrentMonth'] != 6))
    186188        {
    187189          //echo($Mesic.'%'.$MonthCount.'='.($Mesic % $MonthCount).' ');
    188           $PayPerMonth = -($User['plus'] - $User['monthly']);
     190          $PayPerMonth = $User['MonthlyTotal'];
    189191          $PayPerPeriod = $PayPerMonth * $MonthCount;
    190192          $PeriodFrom = mktime(0, 0, 0, date('n'), 1, date('Y'));
    … …  
    193195          $Output .= $TimePeriodText.': '.$PayPerMonth." * ".$MonthCount." = ".$PayPerPeriod."<br />\n";
    194196          $BillCode = $Finance->GetNextDocumentLineNumber(6); // Faktury vydané
    195           $BillId = $this->System->Modules['Bill']->CreateBill($User['id'], array(array('Description' => 'Připojení k síti', 'Price' => $PayPerPeriod, 'Quantity' => 1)), $PeriodFrom, $PeriodTo, $BillCode);
    196           $this->Database->insert('finance_operations', array('money' => -$PayPerPeriod, 'user' => $User['id'], 'type' => 2, 'date' => 'NOW()', 'comment' => 'Připojení k síti za období '.$TimePeriodText, 'group' => 1, 'bill_id' => $BillId, 'BillCode' => $BillCode));
     197          $BillId = $this->System->Modules['Bill']->CreateBill($User['Id'], array(array('Description' => 'Připojení k síti', 'Price' => $PayPerPeriod, 'Quantity' => 1)), $PeriodFrom, $PeriodTo, $BillCode);
     198          $this->Database->insert('finance_operations', array('money' => -$PayPerPeriod, 'user' => $User['Id'], 'type' => 2, 'date' => 'NOW()', 'comment' => 'Připojení k síti za období '.$TimePeriodText, 'group' => 1, 'bill_id' => $BillId, 'BillCode' => $BillCode));
    197199        } else $Output .= ("<br />");
    198200      }
    … …  
    220222      }
    221223
    222       $Output .= "Měním aktuální tarify uživatelů...<br>\n";
    223 
    224224      // Update tariff user selection
    225       $DbResult = $this->Database->query("SELECT id,inet_tarif_next FROM users WHERE role=2");
     225      $Output .= "Měním aktuální tarify uživatelů...<br />\n";
     226      $DbResult = $this->Database->query('SELECT Id, InternetTariffNextMonth FROM Member');
    226227      while($User = $DbResult->fetch_array())
    227228      {
    228         $this->Database->update('users', 'id='.$User['id'], array('inet_tarif_now' => $User['inet_tarif_next']));
     229        $this->Database->update('Member', 'Id='.$User['Id'], array('InternetTariffCurrentMonth' => $User['InternetTariffNextMonth']));
    229230      }
    230231
Note: See TracChangeset for help on using the changeset viewer.