1 | <?php
|
---|
2 | include_once('../global.php');
|
---|
3 |
|
---|
4 | class 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 | ?>
|
---|