source: trunk/Modules/Finance/Finance.php@ 537

Last change on this file since 537 was 537, checked in by chronos, 12 years ago
  • Upraveno: Neinicializovat finance při generování jakékoliv stránky, ale jen těch, které používají modul Finance.
  • Upraveno: Soubor finance.php byl odstraněn a obsah přesunut do Finance.php. Na systémech bez rozlišování velikosti písmen jména soubor nelze mít dva soubory o stejném jméně.
File size: 13.7 KB
Line 
1<?php
2
3include_once(dirname(__FILE__).'/Overview.php');
4include_once(dirname(__FILE__).'/Consumption.php');
5include_once(dirname(__FILE__).'/Devices.php');
6include_once(dirname(__FILE__).'/Bill.php');
7include_once(dirname(__FILE__).'/Services.php');
8include_once(dirname(__FILE__).'/Customers.php');
9include_once(dirname(__FILE__).'/MonthlyOverall.php');
10include_once(dirname(__FILE__).'/Manage.php');
11include_once(dirname(__FILE__).'/UserState.php');
12include_once(dirname(__FILE__).'/Import.php');
13include_once(dirname(__FILE__).'/Zivnost.php');
14include_once(dirname(__FILE__).'/finance.php');
15
16define('TARIFF_FREE', 7);
17define('INVOICE_DUE_DAYS', 15);
18define('INVOICE_OUT_DOC_LINE', 6);
19
20class Finance extends Module
21{
22 var $kWh;
23 var $Internet;
24 var $Sprava;
25 var $DatumOdecteni;
26 var $InternetUsers;
27 var $SpravaUsers;
28 var $InternetSegmentId = 21;
29 var $MaxSpeed;
30 var $RealMaxSpeed;
31 var $SpeedReserve;
32 var $BaseSpeedElement;
33 var $TotalConsumption;
34 var $UserIdNetwork = 46;
35 var $BaseTariffPrice;
36 var $TopTariffPrice;
37 var $TotalPaid;
38 var $TotalInternetPaid;
39 var $Tariffs;
40 var $ExternalSubject = 96;
41 var $MainSubject;
42 var $BillingPeriods;
43 var $DirectoryId;
44
45 function LoadTariffs()
46 {
47 $this->Tariffs = array();
48 $DbResult = $this->Database->select('Service', '*', '`ReplaceId` IS NULL ORDER BY `Name`, `InternetSpeedMax`');
49 while($Tariff = $DbResult->fetch_array())
50 {
51 $Tariff['InternetSpeedMin'] = $Tariff['InternetSpeedMin'] * 1000;
52 $Tariff['InternetSpeedMax'] = $Tariff['InternetSpeedMax'] * 1000;
53 $this->Tariffs[$Tariff['Id']] = $Tariff;
54 }
55 }
56
57 function RecalculateTariffs()
58 {
59 $ResidualSpeed = $this->MaxSpeed * 1000;
60
61 $this->LoadTariffs();
62
63 $Column = array('Current', 'Next');
64 $TotalMemberCount = 0;
65 $TotalMaxSpeed = 0;
66 foreach($this->Tariffs as $Index => $Tariff)
67 {
68 $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Member` '.
69 'LEFT JOIN `ServiceCustomerRel` ON `ServiceCustomerRel`.`Customer`=`Member`.`Id` '.
70 'WHERE (`ServiceCustomerRel`.`Service`='.$Index.') AND (`Member`.`BillingPeriod` > 1) '.
71 'AND (`Member`.`Blocked`=0)');
72 $Row = $DbResult->fetch_row();
73 $this->Tariffs[$Index]['CustomerCount'] = $Row[0];
74 $Tariffs['CustomerCount'] = $Row[0];
75
76 switch($Tariff['Category'])
77 {
78 case 1:
79 $TotalMemberCount += $Tariff['CustomerCount'];
80 $TotalMaxSpeed += $Tariff['InternetSpeedMax'] * $Tariff['CustomerCount'];
81 break;
82 case 2:
83 $ResidualSpeed -= $Tariff['InternetSpeedMin'] * $Tariff['CustomerCount'];
84 break;
85 case 3:
86 break;
87 }
88 }
89 if($TotalMaxSpeed > 0) $Aggregation = $ResidualSpeed / $TotalMaxSpeed;
90 else $Aggregation = 1;
91
92 // Recalculate price
93 foreach($this->Tariffs as $Index => $Tariff)
94 {
95 switch($Tariff['Category'])
96 {
97 case 1:
98 // Přepočítávání rychlostí koliduje s rozdílovým zapisováním stromu front do mikrotiku.
99 // 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.
100 //$Tariff['InternetSpeedMin'] = round($Tariff['InternetSpeedMax'] * $Aggregation);
101 break;
102 case 2:
103 break;
104 case 3:
105 break;
106 }
107 $this->Database->update('Service', 'Id='.$Tariff['Id'],
108 array('InternetSpeedMin' => ($Tariff['InternetSpeedMin'] / 1000),
109 'CustomerCount' => $Tariff['CustomerCount']));
110 }
111 $this->LoadTariffs();
112 }
113
114 function LoadMonthParameters($Period = 1) // 0 - now, 1 - next month
115 {
116 $DbResult = $this->Database->query('SELECT * FROM `FinanceBillingPeriod`');
117 while($BillingPeriod = $DbResult->fetch_assoc())
118 $this->BillingPeriods[$BillingPeriod['Id']] = $BillingPeriod;
119
120 // Period parameter is not used as it have to be determined from item replacement
121 $DbResult = $this->Database->query('SELECT * FROM `FinanceCharge` WHERE `ReplaceId` IS NULL LIMIT 1');
122 $Row = $DbResult->fetch_array();
123 $this->kWh = $Row['kWh'];
124 $this->Internet = $Row['Internet'];
125 $this->Sprava = $Row['AdministrationPerUser'];
126 $this->RealMaxSpeed = $Row['InternetSpeed'];
127 $this->SpeedReserve = $Row['InternetSpeedReserve'];
128 $this->BaseSpeedElement = $Row['BaseSpeedElement'];
129 $this->MaxSpeed = $this->RealMaxSpeed - $this->SpeedReserve;
130 $this->TopTariffPrice = $Row['TopTariffPrice'];
131 $this->BaseTariffPrice = $Row['BaseTariffPrice'];
132
133 $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Member`');
134 $Row = $DbResult->fetch_row();
135 $this->InternetUsers = $Row[0];
136 $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Member` WHERE (`Blocked`=0) AND (`BillingPeriod` > 1)');
137 $Row = $DbResult->fetch_row();
138 $this->PayingUsers = $Row[0];
139
140 $this->SpravaUsers = $this->PayingUsers;
141
142 $DbResult = $this->Database->query('SELECT SUM(`Consumption`) FROM `NetworkSegment`');
143 $TotalConsumption = $DbResult->fetch_array();
144 $this->TotalConsumption = $TotalConsumption[0];
145
146 $DbResult = $this->Database->query('SELECT SUM(`MemberPayment`.`MonthlyInternet`) AS `MonthlyInternet`, '.
147 'SUM(`MemberPayment`.`MonthlyTotal`) AS `MonthlyTotal` '.
148 'FROM `MemberPayment` JOIN `Member` ON `Member`.`Id`=`MemberPayment`.`Member` WHERE `Member`.`Blocked`=0');
149 $Row = $DbResult->fetch_assoc();
150 $this->TotalInternetPaid = $Row['MonthlyInternet'];
151 $this->TotalPaid = $Row['MonthlyTotal'];
152
153 $this->LoadTariffs($Period);
154 }
155
156 function W2Kc($Spotreba)
157 {
158 return(round($Spotreba * 0.72 * $this->kWh));
159 }
160
161 function GetNextDocumentLineNumber($Id, $FinanceYear = 0)
162 {
163 if($FinanceYear == 0)
164 {
165 // Get latest year
166 $DbResult = $this->Database->select('FinanceYear', '*', '1 ORDER BY `Year` DESC LIMIT 1');
167 } else $DbResult = $this->Database->select('FinanceYear', '*', 'Id='.$FinanceYear);
168 $FinanceYear = $DbResult->fetch_assoc();
169
170 $DbResult = $this->Database->query('SELECT `Shortcut`, `Id` FROM `DocumentLine` WHERE `Id`='.$Id);
171 $DocumentLine = $DbResult->fetch_assoc();
172
173 $DbResult = $this->Database->query('SELECT * FROM `DocumentLineSequence` WHERE '.
174 '`DocumentLine`='.$Id.' AND `FinanceYear`='.$FinanceYear['Id']);
175 $Sequence = $DbResult->fetch_assoc();
176
177 if($Sequence['YearPrefix'] == 1)
178 {
179 $Result = $DocumentLine['Shortcut'].$Sequence['NextNumber'].'/'.$FinanceYear['Year'];
180 } else $Result = $DocumentLine['Shortcut'].$Sequence['NextNumber'];
181
182 $this->Database->query('UPDATE `DocumentLineSequence` SET `NextNumber` = `NextNumber` + 1 '.
183 'WHERE `DocumentLine`='.$Id.' AND `FinanceYear`='.$FinanceYear['Id']);
184 return($Result);
185 }
186
187 function RecalculateMemberPayment()
188 {
189 $Output = 'Aktualizuji finance členů...<br />';
190 $this->Database->query('TRUNCATE TABLE `MemberPayment`');
191 $DbResult = $this->Database->query('SELECT * FROM `Member`');
192 while($Member = $DbResult->fetch_assoc())
193 {
194 $DbResult2 = $this->Database->query('SELECT ((SELECT COALESCE(SUM(Value), 0) FROM FinanceOperation '.
195 'WHERE Subject='.$Member['Subject'].') + (SELECT COALESCE(SUM(-Value), 0) FROM FinanceInvoice '.
196 'WHERE Subject='.$Member['Subject'].')) as Cash');
197 $Cash = $DbResult2->fetch_row();
198 $Cash = $Cash[0];
199
200 $DbResult2 = $this->Database->query('SELECT SUM(`Product`.`Consumption`) * `StockItem`.`Amount` '.
201 'FROM `StockItem` JOIN `Product` ON `Product`.`Id` = `StockItem`.`Product` '.
202 'WHERE (`StockItem`.`Location` = '.$Member['Id'].') AND (`StockItem`.`TimeElimination` IS NULL)');
203 $ConsumptionPlus = $DbResult2->fetch_row();
204 $ConsumptionPlus = $ConsumptionPlus[0];
205
206 $NetworkDevice = 0;
207 $Consumption = 0;
208 $Id = $Member['NetworkSegment'];
209 while(($Id != '') and ($Id != 0))
210 {
211 $DbResult2 = $this->Database->query('SELECT * FROM `NetworkSegment` WHERE `Id`='.$Id);
212 $Device = $DbResult2->fetch_assoc();
213 if($Device['Users'] > 0) $NetworkDevice += $Device['Price'] / $Device['Users'];
214 if($Device['UsersOverheads'] > 0) $Consumption += $Device['Consumption'] / $Device['UsersOverheads'];
215 $Id = $Device['Parent'];
216 }
217
218 $DbResult2 = $this->Database->query('SELECT SUM(`Service`.`Price`) AS `Price` '.
219 'FROM `ServiceCustomerRel` LEFT JOIN '.
220 '`Service` ON `Service`.`Id` = `ServiceCustomerRel`.`Service` WHERE `ServiceCustomerRel`.`Customer`='.
221 $Member['Id'].' AND `ServiceCustomerRel`.`Action` IS NULL');
222 $DbRow = $DbResult2->fetch_assoc();
223 $Monthly = 0;
224 if($DbRow['Price'] != '') $MonthlyInet = $DbRow['Price'];
225 else $MonthlyInet = 0;
226
227 $Monthly += $MonthlyInet;
228 $Monthly -= $this->W2Kc($ConsumptionPlus);
229 $Monthly = round($Monthly);
230
231 if($Member['BillingPeriodNext'] == 1)
232 {
233 // Inactive payer
234 $MonthlyInet = 0;
235 $Monthly = 0;
236 $ConsumptionPlus = 0;
237 $Consumption = 0;
238 }
239 $this->Database->insert('MemberPayment', array('Member' => $Member['Id'],
240 'NetworkDevice' => $NetworkDevice, 'MonthlyInternet' => $MonthlyInet,
241 'MonthlyTotal' => $Monthly, 'MonthlyConsumption' => $this->W2Kc($Consumption),
242 'Cash' => $Cash, 'MonthlyPlus' => $this->W2Kc($ConsumptionPlus)));
243 }
244 $this->System->ModuleManager->Modules['Log']->NewRecord('Finance', 'RecalculateMemberPayment');
245 $this->RecalculateTariffs(1);
246 $this->RecalculateTariffs(0);
247 return($Output);
248 }
249
250 function RecalculateSegmentParameters()
251 {
252 $Output = 'Aktualizuji parametry segmentů...<br />';
253 $this->Database->query('UPDATE `NetworkSegment` SET `Users` = 0, `UsersOverheads` = 0'); // Vynulovat počty uživatelů
254 $DbResult = $this->Database->query('SELECT * FROM `NetworkSegment`');
255 while($NetworkSegment = $DbResult->fetch_array())
256 {
257 $DbResult2 = $this->Database->query('SELECT `Users` FROM `NetworkSegment` WHERE `Id`='.$NetworkSegment['Id']);
258 $RowP = $DbResult2->fetch_array();
259 $DbResult2 = $this->Database->query('SELECT `UsersOverheads` FROM `NetworkSegment` WHERE `Id`='.$NetworkSegment['Id']);
260 $RowP2 = $DbResult2->fetch_array();
261
262 $DbResult2 = $this->Database->query('SELECT SUM(`Product`.`BuyPrice`) * `StockItem`.`Amount` AS `Price`, '.
263 'SUM(`Product`.`Consumption`) * `StockItem`.`Amount` AS `Consumption` '.
264 'FROM `StockItem` JOIN `Product` ON `Product`.`Id` = `StockItem`.`Product` '.
265 'WHERE (`StockItem`.`Segment`='.$NetworkSegment['Id'].') AND (`StockItem`.`TimeElimination` IS NULL)');
266 $Row2 = $DbResult2->fetch_array();
267 $DbResult2 = $this->Database->query('SELECT COUNT(*) FROM Member WHERE NetworkSegment='.$NetworkSegment['Id']);
268 $Row3 = $DbResult2->fetch_array();
269 $ID = $NetworkSegment['Parent'];
270 while($ID != 0)
271 {
272 $DbResult2 = $this->Database->query('SELECT * FROM NetworkSegment WHERE Id='.$ID);
273 $Row4 = $DbResult2->fetch_array();
274 $this->Database->update('NetworkSegment', 'Id='.$Row4['Id'],
275 array('Users' => ($Row4['Users'] + $Row3[0]), 'UsersOverheads' => ($Row4['UsersOverheads'] + $Row3[0])));
276 $ID = $Row4['Parent'];
277 }
278 $this->Database->update('NetworkSegment', 'Id='.$NetworkSegment['Id'],
279 array('Price' => $Row2['Price'], 'Users' => ($Row3[0] + $RowP['Users']), 'Consumption' => $Row2['Consumption'], 'UsersOverheads' => ($Row3[0] + $RowP2['UsersOverheads'])));
280 }
281
282 // Zkorigovat segment Internet
283 $DbResult = $this->Database->select('Member', 'COUNT(*)');
284 $Row = $DbResult->fetch_array();
285 $DbResult = $this->Database->update('NetworkSegment', 'Id='.$this->InternetSegmentId,
286 array('Users' => $Row[0], 'UsersOverheads' => $Row[0]));
287 $this->System->ModuleManager->Modules['Log']->NewRecord('Finance', 'RecalculateSegmentParameters');
288 return($Output);
289 }
290}
291
292class ModuleFinance extends AppModule
293{
294 function __construct($System)
295 {
296 parent::__construct($System);
297 $this->Name = 'Finance';
298 $this->Version = '1.0';
299 $this->Creator = 'Chronos';
300 $this->License = 'GNU/GPLv3';
301 $this->Description = 'Base module for finance management';
302 $this->Dependencies = array('File');
303 }
304
305 function Install()
306 {
307 }
308
309 function Uninstall()
310 {
311 }
312
313 function Start()
314 {
315 global $Config;
316
317 parent::Start();
318 $this->System->RegisterPage('finance', 'PageFinance');
319 $this->System->RegisterPage(array('finance', 'spotreba'), 'PageFinanceConsumption');
320 $this->System->RegisterPage(array('finance', 'zarizeni'), 'PageFinanceDeviceList');
321 $this->System->RegisterPage(array('finance', 'sluzby'), 'PageFinanceServices');
322 $this->System->RegisterPage(array('finance', 'mesicni-prehledy'), 'PageFinanceMonthlyOverall');
323 $this->System->RegisterPage(array('finance', 'zakaznici'), 'PageFinanceCustomers');
324 $this->System->RegisterPage(array('finance', 'sprava'), 'PageFinanceManage');
325 $this->System->RegisterPage(array('finance', 'platby'), 'PageFinanceUserState');
326 $this->System->RegisterPage(array('finance', 'import'), 'PageFinanceImportPayment');
327 $this->System->RegisterPage(array('finance', 'zivnost'), 'PageFinanceTaxFiling');
328
329 $this->System->AddModule(new Bill($this->System));
330 $this->System->AddModule(new Finance($this->System));
331 $this->System->Modules['Finance']->MainSubject = $Config['Finance']['MainSubjectId'];
332 $this->System->Modules['Finance']->DirectoryId = $Config['Finance']['DirectoryId'];
333 }
334
335 function Stop()
336 {
337 }
338}
339
340?>
Note: See TracBrowser for help on using the repository browser.