source: trunk/finance/finance.php@ 185

Last change on this file since 185 was 185, checked in by george, 16 years ago
  • Opraveno: Přepočítávání stavu financí členů a generování periodických plateb.
File size: 10.1 KB
Line 
1<?php
2
3class Finance extends Module
4{
5 var $kWh;
6 var $Internet;
7 var $Sprava;
8 var $DatumOdecteni;
9 var $InternetUsers;
10 var $SpravaUsers;
11 var $InternetSegmentId = 21;
12 var $MaxSpeed;
13 var $RealMaxSpeed;
14 var $SpeedReserve;
15 var $BaseSpeedElement;
16 var $TotalConsumption;
17 var $UserIdNetwork = 46;
18 var $BaseTariffPrice;
19 var $TopTariffPrice;
20 var $TotalPaid;
21 var $TotalInternetPaid;
22 var $Tarify;
23 var $ExternalSubject = 96;
24 var $MainSubject = 71;
25 var $BillingPeriods;
26
27 function RecalculateTariffs($Period = 1)
28 {
29 $ResidualPrice = $this->Internet;
30 $ResidualSpeed = $this->MaxSpeed;
31
32 $Tarify = array();
33 $DbResult = $this->Database->select('finance_tariffs', '*', 'period='.$Period.' ORDER BY id,speed_factor');
34 while($Tariff = $DbResult->fetch_array())
35 {
36 $Tarify[$Tariff['id']] = $Tariff;
37 }
38
39 if($Period == 0) $Column = 'Current'; else $Column = 'Next';
40 $TotalUserCount = 0;
41 $TotalUnits = 0;
42 $TotalTarifUnits = 0;
43 $TotalTarifSpeedUnits = 0;
44 foreach($Tarify as $Index => $Tarif)
45 {
46 $DbResult = $this->Database->select('Member', 'COUNT(*)', 'InternetTariff'.$Column.'Month='.$Index);
47 $Row = $DbResult->fetch_row();
48 $Tarify[$Index]['user_count'] = $Row[0];
49 switch($Tarif['group_id'])
50 {
51 case 1:
52 $TotalUserCount = $TotalUserCount + $Tarify[$Index]['user_count'];
53 $Tarify[$Index]['total_units'] = $Tarify[$Index]['price_units'] * $Tarify[$Index]['user_count'];
54 $TotalTarifUnits = $TotalTarifUnits + $Tarify[$Index]['total_units'];
55
56 if($Index == 3) // Zarizeni site v tarifu 3 neplati penize, ale ma internet
57 $Tarify[$Index]['total_speed_factor'] = $Tarify[$Index]['speed_factor'] * ($Tarify[$Index]['user_count']+1);
58 else $Tarify[$Index]['total_speed_factor'] = $Tarify[$Index]['speed_factor'] * $Tarify[$Index]['user_count'];
59 $TotalTarifSpeedUnits = $TotalTarifSpeedUnits + $Tarify[$Index]['total_speed_factor'];
60 break;
61 case 2:
62 $ResidualPrice -= $Tarif['price'] * $Tarify[$Index]['user_count'];
63 $ResidualSpeed -= $Tarif['min_speed'] * $Tarify[$Index]['user_count'];
64 $Tarify[$Index]['total_units'] = $Tarify[$Index]['price_units'] * $Tarify[$Index]['user_count'];
65 break;
66 case 3:
67 $Tarify[$Index]['total_units'] = $Tarify[$Index]['price_units'] * $Tarify[$Index]['user_count'];
68 break;
69 }
70 }
71 //$PricePerUnit = $TopTariffPrice / ; // $ResidualPrice / $TotalTarifUnits;
72 $SpeedPerUnit = $ResidualSpeed * 1024 / $TotalTarifSpeedUnits;
73
74 // Recalculate price
75 foreach($Tarify as $Index => $Tarif)
76 {
77 switch($Tarif['group_id'])
78 {
79 case 1:
80 $Tarify[$Index]['price'] = $this->BaseTariffPrice + round($this->TopTariffPrice * $Tarif['price_units'] / 10) * 10;
81 $Tarify[$Index]['min_speed'] = round($Tarif['speed_factor'] * $SpeedPerUnit);
82 $Tarify[$Index]['max_speed'] = round($Tarify[$Index]['speed_factor'] * $this->BaseSpeedElement * 1024);
83 break;
84 case 2:
85 $Tarify[$Index]['min_speed'] = $Tarify[$Index]['min_speed'] * 1024;
86 $Tarify[$Index]['max_speed'] = $Tarify[$Index]['max_speed'] * 1024;
87 break;
88 case 3:
89 $Tarify[$Index]['price'] = $this->BaseTariffPrice + round($this->TopTariffPrice * $Tarif['price_units']);
90 $Tarify[$Index]['min_speed'] = $Tarify[$Index]['min_speed'] * 1024;
91 $Tarify[$Index]['max_speed'] = $Tarify[$Index]['max_speed'] * 1024;
92 break;
93 }
94 $Tarify[$Index]['aggregation'] = '1:'.round($Tarify[$Index]['max_speed'] / $Tarify[$Index]['min_speed']);
95 }
96 $this->Tarify = $Tarify;
97 }
98
99 function LoadMonthParameters($Period = 1) // 0 - now, 1 - next month
100 {
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);
106 $Row = $DbResult->fetch_array();
107 $this->kWh = $Row['kWh'];
108 $this->Internet = $Row['internet'];
109 $this->Sprava = $Row['administration_per_user'];
110 $this->RealMaxSpeed = $Row['internet_speed'];
111 $this->SpeedReserve = $Row['internet_speed_reserve'];
112 $this->BaseSpeedElement = $Row['base_speed_element'];
113 $this->MaxSpeed = $this->RealMaxSpeed - $this->SpeedReserve;
114 $this->TopTariffPrice = $Row['TopTariffPrice'];
115 $this->BaseTariffPrice = $Row['BaseTariffPrice'];
116
117 $DbResult = $this->Database->query('SELECT COUNT(*) FROM Member');
118 $Row = $DbResult->fetch_row();
119 $this->InternetUsers = $Row[0];
120
121 $this->SpravaUsers = $this->InternetUsers;
122 $DbResult = $this->Database->query('SELECT SUM(consumption) FROM network_segments');
123 $TotalConsumption = $DbResult->fetch_array();
124 $this->TotalConsumption = $TotalConsumption[0];
125
126 $DbResult = $this->Database->query('SELECT SUM(`MonthlyInternet`) AS `MonthlyInternet`, SUM(`MonthlyTotal`) AS `MonthlyTotal` FROM MemberPayment');
127 $Row = $DbResult->fetch_assoc();
128 $this->TotalInternetPaid = $Row['MonthlyInternet'];
129 $this->TotalPaid = $Row['MonthlyTotal'];
130
131 $this->RecalculateTariffs($Period);
132 }
133
134 function W2Kc($Spotreba)
135 {
136 return(round($Spotreba * 0.72 * $this->kWh));
137 }
138
139 function GetNextDocumentLineNumber($Id)
140 {
141 $DbResult = $this->Database->query('SELECT Shortcut, NextNumber FROM DocumentLine WHERE Id='.$Id);
142 $DbRow = $DbResult->fetch_assoc();
143 $Result = $DbRow['Shortcut'].$DbRow['NextNumber'];
144 $this->Database->query('UPDATE DocumentLine SET NextNumber = NextNumber + 1 WHERE Id='.$Id);
145 return($Result);
146 }
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 }
229}
230
231?>
Note: See TracBrowser for help on using the repository browser.