source: www/finance/prepocet.php@ 157

Last change on this file since 157 was 157, checked in by george, 16 years ago
  • Upraveno: Soubor include.php ve financích přepracován na třídy ve finance.php.
  • Property svn:executable set to *
File size: 13.3 KB
Line 
1<?php
2include_once('../global.php');
3
4class FinanceRecalculatePage extends Page
5{
6 var $FullTitle = 'Přepočet financí';
7 var $ShortTitle = 'Přepočet financí';
8
9 function RecalculateUsersFinance()
10 {
11 $Finance = &$this->System->Modules['Finance'];
12 $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;
22 $Consumption = 0;
23 $ID = $Row['network_segment'];
24 while($ID != 0)
25 {
26 $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 }
33 //echo($ID.' '.$InternetSegment.' '.$InternetSegmentId.' '.$Row['inet_hw'].' '.$Price.'<br>');
34 $ID = $Row2['parent'];
35 }
36 $Monthly = 0;
37 $MonthlyInet = $Finance->Tarify[$Row['inet_tarif_next']]['price'];
38 if($Row['inet'] == 1) $Monthly += $MonthlyInet;
39 //if($Row['overheads'] == 1) $Monthly += $Sprava; // + W2Kc($Consumption);
40 //echo($Row['fullname'].' '.$Row['inet'].' '.$Monthly.'<br>');
41 $Monthly = round($Monthly);
42 //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])));
44 }
45 return($Output);
46 }
47
48 function RecalculateSegmentParameters()
49 {
50 $Finance = &$this->System->Modules['Finance'];
51 $Output = 'Aktualizuju parametry segmentů...<br />';
52 $this->Database->query('UPDATE network_segments SET users = 0, users_overheads = 0'); // Vynulovat počty uživatelů
53 $DbResult = $this->Database->query('SELECT * FROM network_segments');
54 while($Row = $DbResult->fetch_array())
55 {
56 //echo('Segment '.$Row['name'].'<br>');
57 $DbResult2 = $this->Database->query('SELECT users FROM network_segments WHERE id='.$Row['id']);
58 $RowP = $DbResult2->fetch_array();
59 $DbResult2 = $this->Database->query("SELECT users_overheads FROM network_segments WHERE id=".$Row['id']);
60 $RowP2 = $DbResult2->fetch_array();
61
62 $DbResult2 = $this->Database->query("SELECT SUM(price) as Price, SUM(consumption) as Consumption FROM network_devices WHERE segment=".$Row['id']." AND used=1");
63 $Row2 = $DbResult2->fetch_array();
64 $DbResult2 = $this->Database->query("SELECT COUNT(*) FROM users WHERE network_segment=".$Row['id'].' AND hw=1');
65 $Row3 = $DbResult2->fetch_array();
66 $DbResult2 = $this->Database->query("SELECT COUNT(*) FROM users WHERE network_segment=".$Row['id'].' AND overheads=1');
67 $Row5 = $DbResult2->fetch_array();
68 $ID = $Row['parent'];
69 while($ID != 0)
70 {
71 //echo($ID.', ');
72 $DbResult2 = $this->Database->query("SELECT * FROM network_segments WHERE id=".$ID);
73 $Row4 = $DbResult2->fetch_array();
74 $this->Database->update('network_segments', 'id='.$Row4['id'], array('users' => ($Row4['users'] + $Row3[0]), 'users_overheads' => ($Row4['users_overheads'] + $Row5[0])));
75 $ID = $Row4['parent'];
76 }
77 //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'])));
79 }
80
81 // Zkorigovat segment Internet
82 $DbResult = $this->Database->select('users', 'COUNT(*)', 'inet_hw=1');
83 $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]));
87 return($Output);
88 }
89
90 function CreateMonthlyOverallBill()
91 {
92 global $Sprava, $SpravaUsers, $TotalConsumption, $UserIdNetwork, $Internet, $TotalInternetPaid;
93
94 $TotalConsumptionPrice = $this->System->Modules['Finance']->W2Kc($TotalConsumption);
95
96 $Output .= "Spotřeba energie: ".-$TotalConsumptionPrice."<br />";
97 $Output .= "Správa sítě: ".-($Sprava * $SpravaUsers)."<br>";
98 $Output .= "Internet: ".-$Internet."<br>";
99 $Output .= "Poplatky učástníků: ".$TotalInternetPaid."<br>";
100
101 $this->Database->insert('finance_operations', array('money' => -$TotalConsumptionPrice, 'user' => $UserIdNetwork, 'type' => 2, 'date' => 'NOW()', 'comment' => 'Spotřeba energie', 'group' => 2));
102 $this->Database->insert('finance_operations', array('money' => -($Sprava * $SpravaUsers), 'user' => $UserIdNetwork, 'type' => 2, 'date' => 'NOW()', 'comment' => 'Správa sítě', 'group' => 2));
103 $this->Database->insert('finance_operations', array('money' => -$Internet, 'user' => $UserIdNetwork, 'type' => 2, 'date' => 'NOW()', 'comment' => 'Internet', 'group' => 2));
104 $this->Database->insert('finance_operations', array('money' => $TotalInternetPaid, 'user' => $UserIdNetwork, 'type' => 2, 'date' => 'NOW()', 'comment' => 'Poplatky učástníků', 'group' => 2));
105 return($Output);
106 }
107
108 function DiscountNetworkDevicePrice()
109 {
110 $Output = '';
111 $DbResult = $this->Database->query("SELECT *,CONCAT(second_name, ' ', first_name) as fullname FROM users WHERE role=2");
112 while($Row = $DbResult->fetch_array())
113 {
114 $Output .= $Row['fullname'].': '.($Row['network_device'])."<br />";
115 $this->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));
116 //echo($Database->LastQuery."<br>\n");
117 }
118 return($Output);
119 }
120
121
122 function Show()
123 {
124 if(!$this->System->Modules['User']->CheckPermission('Finance', 'Manage')) return('Nemáte oprávnění');
125 $Output = '';
126
127 $Finance = &$this->System->Modules['Finance'];
128
129 // Načti poslední měsíční přehled a nastavení
130 $DbResult = $this->Database->select('finance_monthly_overall', '*', '1 ORDER BY date DESC LIMIT 1');
131 $Overall = $DbResult->fetch_array();
132
133 $Output -= 'Datum: '.date('j.n.Y')."<br />";
134
135 $Datum = explode('-', $Overall['date']);
136 $Mesic = date("m") + 0;
137
138 $Output .= $this->RecalculateSegmentParameters();
139 $Output .= $Finance->RecalculateTariffs();
140 //DiscountNetworkDevicePrice();
141 //$InvoiceGenerator->CustomGenerate();
142 $Output .= $this->RecalculateUsersFinance();
143
144 // Celkovy prehled
145 $DbResult = $this->Database->query("SELECT SUM(price) FROM network_devices WHERE used=1");
146 $Row4 = $DbResult->fetch_array();
147 $Output .= "Celková cena zařízení: Zařízení(".$Row4[0];
148 $DbResult = $this->Database->query("SELECT SUM(price) FROM network_segments");
149 $Row = $DbResult->fetch_array();
150 $Output .= "), Segmenty(".$Row[0];
151 $DbResult = $this->Database->query("SELECT SUM(network_device) FROM users");
152 $Row5 = $DbResult->fetch_array();
153 $Output .= "), Uživatelé(".$Row5[0].")<br>\n";
154 $DbResult = $this->Database->query("SELECT SUM(cash) FROM users WHERE role=2");
155 $Row6 = $DbResult->fetch_array();
156 $Output .= "Stav pokladny: Uživatelé(".$Row6[0].")";
157 $DbResult = $this->Database->query("SELECT SUM(money) FROM finance_operations WHERE `group`=1"); // Položky uživatelů
158 $Row2 = $DbResult->fetch_array();
159 $Output .= ", Operace-Zařízení(".($Row2[0] - $Row4[0]).")<br>\n";
160 $Output .= "Rozdíl peněz v operacích: ".($Row2[0] - $Row6[0] - $Row4[0])." <br>\n";
161
162
163 $DbResult = $this->Database->query("SELECT SUM(consumption) FROM network_devices WHERE used=1");
164 $Row = $DbResult->fetch_array();
165 $CelkemSpotreba = $Finance->W2Kc($Row[0]);
166
167 $SpravaCelkem = $Finance->Sprava * $Finance->SpravaUsers;
168 $Output .= "Kontrola placení (Zaplaceno-Sprava-Internet): ".$Finance->TotalPaid."-".$SpravaCelkem."-".$Finance->Internet."=".($Finance->TotalPaid - $SpravaCelkem - $Finance->Internet)."<br>\n\n";
169
170 // Zkontrolovat odečtení měsíčního poplatku
171 //$Mesic = '1';
172 $Output .= 'Kontrola odečtení poplatků: Poslední měsíc-'.round($Datum[1]).' Aktuální měsíc-'.$Mesic."<br>\n";
173 if($Mesic != $Datum[1])
174 {
175 $Output .= "Odečítám měsíční poplatek...<br>\n";
176
177 $BillingPeriodMonthCount = array('never' => 0, 'monthly' => 1, 'quarterly' => 3, 'half-yearly' => 6, 'yearly' => 12);
178
179 // Generuj účetní položky
180 $DbResult = $this->Database->query("SELECT *, CONCAT(second_name, ' ', first_name) as fullname FROM users WHERE (role = 2)");
181 while($User = $DbResult->fetch_array())
182 {
183 $Output .= $User['fullname'].': ';
184 $MonthCount = $BillingPeriodMonthCount[$User['BillingPeriod']];
185 if(($MonthCount > 0) and (((($Mesic - 1) % 12) % $MonthCount) == 0) and ($User['inet_tarif_now'] != 6))
186 {
187 //echo($Mesic.'%'.$MonthCount.'='.($Mesic % $MonthCount).' ');
188 $PayPerMonth = -($User['plus'] - $User['monthly']);
189 $PayPerPeriod = $PayPerMonth * $MonthCount;
190 $PeriodFrom = mktime(0, 0, 0, date('n'), 1, date('Y'));
191 $PeriodTo = mktime(0, 0, 0, date('n') + $MonthCount - 1, date('t', mktime(0, 0, 0, date('n') + $MonthCount - 1, 1, date('Y'))) , date('Y'));
192 $TimePeriodText = date('j.n.Y', $PeriodFrom).' - '.date('j.n.Y', $PeriodTo);
193 $Output .= $TimePeriodText.': '.$PayPerMonth." * ".$MonthCount." = ".$PayPerPeriod."<br />\n";
194 $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 } else $Output .= ("<br />");
198 }
199 $Output .= 'Měním aktuální parametry sítě...<br>\n';
200
201 // Update finance charge
202 $this->Database->delete('finance_charge', 'period=0');
203 $DbResult = $this->Database->select('finance_charge', '*', 'period=1');
204 $Charge = $DbResult->fetch_array();
205 //print_r($Charge);
206 $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']));
207
208 $Output .= "Přidávám měsíční přehled...<br>\n";
209
210 $this->Database->insert('finance_monthly_overall', array('date' => 'NOW()', 'money' => $Finance->Internet, 'kWh' => $Finance->kWh, 'administration' => $Finance->Sprava, 'administration_total' => $SpravaCelkem, 'consumption_total' => $CelkemSpotreba, 'total_paid' => $Finance->TotalPaid, 'BaseTariffPrice' => $Charge['BaseTariffPrice'], 'TopTariffPrice' => $Charge['TopTariffPrice'], 'member_count' => $Finance->InternetUsers));
211
212 $Output .= "Měním aktuální tarify....<br>\n";
213
214 // Update tarrifs
215 $this->Database->delete('finance_tariffs', 'period=0');
216 $DbResult = $this->Database->select('finance_tariffs', '*', 'period=1');
217 while($Tariff = $DbResult->fetch_array())
218 {
219 $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']));
220 }
221
222 $Output .= "Měním aktuální tarify uživatelů...<br>\n";
223
224 // Update tariff user selection
225 $DbResult = $this->Database->query("SELECT id,inet_tarif_next FROM users WHERE role=2");
226 while($User = $DbResult->fetch_array())
227 {
228 $this->Database->update('users', 'id='.$User['id'], array('inet_tarif_now' => $User['inet_tarif_next']));
229 }
230
231 $Output .= $this->System->Modules['Finance']->RecalculateTariffs();
232 $this->RecalculateUsersFinance();
233 //CreateMonthlyOverallBill();
234 $this->RecalculateUsersFinance();
235
236 // Restart traffic shaping
237 $this->Database->update('services_restart', 'id=3', array('changed' => 1));
238 }
239 return($Output);
240 }
241}
242
243$System->AddModule(new FinanceRecalculatePage());
244$System->Modules['FinanceRecalculatePage']->GetOutput();
245
246?>
Note: See TracBrowser for help on using the repository browser.