source: trunk/finance/finance.php@ 275

Last change on this file since 275 was 275, checked in by george, 15 years ago
  • Upraveno: Nově rozšířená tabulka dokladových řad pro podporu oddělených účetních roků.
  • Opraveno: Nové volání funkcí pro IPv4 ze skriptů generování nastavení sítě.
File size: 10.0 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 $Tariffs;
23 var $ExternalSubject = 96;
24 var $MainSubject = 71;
25 var $BillingPeriods;
26
27 function LoadTariffs($Period = 1)
28 {
29 $this->Tariffs = array();
30 $DbResult = $this->Database->select('FinanceTariff', '*', 'Period='.$Period.' ORDER BY Tariff, SpeedMax');
31 while($Tariff = $DbResult->fetch_array())
32 {
33 $Tariff['SpeedMin'] = $Tariff['SpeedMin'] * 1024;
34 $Tariff['SpeedMax'] = $Tariff['SpeedMax'] * 1024;
35 $this->Tariffs[$Tariff['Tariff']] = $Tariff;
36 }
37 }
38
39 function RecalculateTariffs($Period = 1)
40 {
41 $ResidualSpeed = $this->MaxSpeed * 1024;
42
43 $this->LoadTariffs($Period);
44
45 $Column = array('Current', 'Next');
46 $TotalMemberCount = 0;
47 $TotalMaxSpeed = 0;
48 foreach($this->Tariffs as $Index => $Tariff)
49 {
50 $DbResult = $this->Database->select('Member', 'COUNT(*)', 'InternetTariff'.$Column[$Period].'Month='.$Index);
51 $Row = $DbResult->fetch_row();
52 $this->Tariffs[$Index]['MemberCount'] = $Row[0];
53 $Tariffs['MemberCount'] = $Row[0];
54
55 //echo($Tariff['Name'].' '.$Tariff['MemberCount'].' '.$Tariff['SpeedMax'] * $Tariff['MemberCount'].' '.$ResidualSpeed.'<br />');
56 switch($Tariff['Group'])
57 {
58 case 1:
59 $TotalMemberCount += $Tariff['MemberCount'];
60 $TotalMaxSpeed += $Tariff['SpeedMax'] * $Tariff['MemberCount'];
61 break;
62 case 2:
63 $ResidualSpeed -= $Tariff['SpeedMin'] * $Tariff['MemberCount'];
64 break;
65 case 3:
66 break;
67 }
68 }
69 $Aggregation = $ResidualSpeed / $TotalMaxSpeed;
70 //echo($TotalMaxSpeed.' '.$Aggregation.'<br />');
71
72 // Recalculate price
73 foreach($this->Tariffs as $Index => $Tariff)
74 {
75 switch($Tariff['Group'])
76 {
77 case 1:
78 // Přepočítávání rychlostí koliduje s rozdílovým zapisováním stromu front do mikrotiku. Vždy při změně počtu počítačů či domácností docházelo ke změně minima a přepočtu všeho.
79 //$Tariff['SpeedMin'] = round($Tariff['SpeedMax'] * $Aggregation);
80 break;
81 case 2:
82 break;
83 case 3:
84 break;
85 }
86 //echo('MinSpeed: '.$Tariff['SpeedMin'].'<br />');
87 $this->Database->update('FinanceTariff', 'Tariff='.$Tariff['Tariff'], array('SpeedMin' => ($Tariff['SpeedMin'] / 1024), 'MemberCount' => $Tariff['MemberCount']));
88 }
89 $this->LoadTariffs($Period);
90 }
91
92 function LoadMonthParameters($Period = 1) // 0 - now, 1 - next month
93 {
94 $DbResult = $this->Database->query('SELECT * FROM FinanceBillingPeriod');
95 while($BillingPeriod = $DbResult->fetch_assoc())
96 $this->BillingPeriods[$BillingPeriod['Id']] = $BillingPeriod;
97
98 $DbResult = $this->Database->query('SELECT * FROM finance_charge WHERE period='.$Period);
99 $Row = $DbResult->fetch_array();
100 $this->kWh = $Row['kWh'];
101 $this->Internet = $Row['internet'];
102 $this->Sprava = $Row['administration_per_user'];
103 $this->RealMaxSpeed = $Row['internet_speed'];
104 $this->SpeedReserve = $Row['internet_speed_reserve'];
105 $this->BaseSpeedElement = $Row['base_speed_element'];
106 $this->MaxSpeed = $this->RealMaxSpeed - $this->SpeedReserve;
107 $this->TopTariffPrice = $Row['TopTariffPrice'];
108 $this->BaseTariffPrice = $Row['BaseTariffPrice'];
109
110 $DbResult = $this->Database->query('SELECT COUNT(*) FROM Member');
111 $Row = $DbResult->fetch_row();
112 $this->InternetUsers = $Row[0];
113
114 $this->SpravaUsers = $this->InternetUsers;
115 $DbResult = $this->Database->query('SELECT SUM(consumption) FROM network_segments');
116 $TotalConsumption = $DbResult->fetch_array();
117 $this->TotalConsumption = $TotalConsumption[0];
118
119 $DbResult = $this->Database->query('SELECT SUM(`MonthlyInternet`) AS `MonthlyInternet`, SUM(`MonthlyTotal`) AS `MonthlyTotal` FROM MemberPayment');
120 $Row = $DbResult->fetch_assoc();
121 $this->TotalInternetPaid = $Row['MonthlyInternet'];
122 $this->TotalPaid = $Row['MonthlyTotal'];
123
124 $this->LoadTariffs($Period);
125 }
126
127 function W2Kc($Spotreba)
128 {
129 return(round($Spotreba * 0.72 * $this->kWh));
130 }
131
132 function GetNextDocumentLineNumber($Id, $FinanceYear = 0)
133 {
134 if($FinanceYear == 0)
135 {
136 // Get latest year
137 $DbResult = $this->Database->select('FinanceYear', '*', '1 ORDER BY Year DESC LIMIT 1');
138 } else $DbResult = $this->Database->select('FinanceYear', '*', 'Id='.$FinanceYear);
139 $FinanceYear = $DbResult->fetch_assoc();
140
141 $DbResult = $this->Database->query('SELECT Shortcut, Id FROM DocumentLine WHERE Id='.$Id);
142 $DocumentLine = $DbResult->fetch_assoc();
143
144 $DbResult = $this->Database->query('SELECT Shortcut FROM DocumentLineSequence WHERE DocumentLine='.Id.' AND FinanceYear'.$FinanceYear['Id']);
145 $Sequence = $DbResult->fetch_assoc();
146
147 if($Sequence['YearPrefix'] == 1)
148 {
149 $Result = $DbRow['Shortcut'].$Sequence['NextNumber'].'/'.$FinanceYear['Year'];
150 } else $Result = $DocumentLine['Shortcut'].$Sequence['NextNumber'];
151
152 $this->Database->query('UPDATE DocumentLineSequence SET NextNumber = NextNumber + 1 WHERE DocumentLine='.$Id.' AND FinanceYear='.$FinanceYear['Id']);
153 return($Result);
154 }
155
156 function RecalculateMemberPayment()
157 {
158 $Output = 'Aktualizuji finance členů...<br />';
159 $this->Database->query('TRUNCATE TABLE MemberPayment');
160 $DbResult = $this->Database->query('SELECT * FROM Member');
161 while($Member = $DbResult->fetch_assoc())
162 {
163 $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');
164 $Cash = $DbResult2->fetch_row();
165 $Cash = $Cash[0];
166
167 $DbResult2 = $this->Database->query('SELECT SUM(consumption) FROM network_devices WHERE (user='.$Member['Id'].') AND (used = 1)');
168 $ConsumptionPlus = $DbResult2->fetch_row();
169 $ConsumptionPlus = $ConsumptionPlus[0];
170
171 $NetworkDevice = 0;
172 $Consumption = 0;
173 $ID = $Member['NetworkSegment'];
174 while($ID != 0)
175 {
176 $DbResult2 = $this->Database->query('SELECT * FROM network_segments WHERE id='.$ID);
177 $Device = $DbResult2->fetch_assoc();
178 $NetworkDevice += $Device['price'] / $Device['users'];
179 $Consumption += $Device['consumption'] / $Device['users_overheads'];
180 //echo($ID.' '.$InternetSegment.' '.$InternetSegmentId.' '.$Row['inet_hw'].' '.$Price.'<br>');
181 $ID = $Device['parent'];
182 }
183
184 $Monthly = 0;
185 $MonthlyInet = $this->Tariffs[$Member['InternetTariffNextMonth']]['Price'];
186 //if($Row['inet'] == 1)
187 $Monthly += $MonthlyInet;
188 $Monthly -= $this->W2Kc($ConsumptionPlus);
189 $Monthly -= $Member['Hire'];
190 //if($Row['overheads'] == 1) $Monthly += $Sprava; // + W2Kc($Consumption);
191 //echo($Row['fullname'].' '.$Row['inet'].' '.$Monthly.'<br>');
192 $Monthly = round($Monthly);
193 //echo($Row['fullname'].' '.$Row['inet'].' '.$Monthly.'<br>');
194
195 $this->Database->insert('MemberPayment', array('Member' => $Member['Id'], 'NetworkDevice' => $NetworkDevice, 'MonthlyInternet' => $MonthlyInet, 'MonthlyTotal' => $Monthly, 'MonthlyConsumption' => $this->W2Kc($Consumption), 'Cash' => $Cash, 'MonthlyPlus' => $this->W2Kc($ConsumptionPlus)));
196 }
197 $this->System->Modules['Log']->NewRecord('Finance', 'RecalculateMemberPayment');
198 $this->RecalculateTariffs(1);
199 $this->RecalculateTariffs(0);
200 return($Output);
201 }
202
203 function RecalculateSegmentParameters()
204 {
205 $Output = 'Aktualizuji parametry segmentů...<br />';
206 $this->Database->query('UPDATE network_segments SET users = 0, users_overheads = 0'); // Vynulovat počty uživatelů
207 $DbResult = $this->Database->query('SELECT * FROM network_segments');
208 while($NetworkSegment = $DbResult->fetch_array())
209 {
210 //echo('Segment '.$Row['name'].'<br>');
211 $DbResult2 = $this->Database->query('SELECT users FROM network_segments WHERE id='.$NetworkSegment['id']);
212 $RowP = $DbResult2->fetch_array();
213 $DbResult2 = $this->Database->query('SELECT users_overheads FROM network_segments WHERE id='.$NetworkSegment['id']);
214 $RowP2 = $DbResult2->fetch_array();
215
216 $DbResult2 = $this->Database->query('SELECT SUM(price) as Price, SUM(consumption) as Consumption FROM network_devices WHERE segment='.$NetworkSegment['id'].' AND used=1');
217 $Row2 = $DbResult2->fetch_array();
218 $DbResult2 = $this->Database->query('SELECT COUNT(*) FROM Member WHERE NetworkSegment='.$NetworkSegment['id']);
219 $Row3 = $DbResult2->fetch_array();
220 $ID = $NetworkSegment['parent'];
221 while($ID != 0)
222 {
223 //echo($ID.', ');
224 $DbResult2 = $this->Database->query('SELECT * FROM network_segments WHERE id='.$ID);
225 $Row4 = $DbResult2->fetch_array();
226 $this->Database->update('network_segments', 'id='.$Row4['id'], array('users' => ($Row4['users'] + $Row3[0]), 'users_overheads' => ($Row4['users_overheads'] + $Row3[0])));
227 $ID = $Row4['parent'];
228 }
229 $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'])));
230 }
231
232 // Zkorigovat segment Internet
233 $DbResult = $this->Database->select('Member', 'COUNT(*)');
234 $Row = $DbResult->fetch_array();
235 $DbResult = $this->Database->update('network_segments','id='.$this->InternetSegmentId, array('users' => $Row[0], 'users_overheads' => $Row[0]));
236 $this->System->Modules['Log']->NewRecord('Finance', 'RecalculateSegmentParameters');
237 return($Output);
238 }
239}
240
241?>
Note: See TracBrowser for help on using the repository browser.