source: trunk/finance/manage.php@ 186

Last change on this file since 186 was 186, checked in by george, 16 years ago
  • Přidáno: Odkazy na stránky občanů a doplnění některých favicon.
File size: 27.6 KB
Line 
1<?php
2include_once('../global.php');
3
4class FinanceManagePage extends Page
5{
6 var $FullTitle = 'Správa financí';
7 var $ShortTitle = 'Správa financí';
8
9 function Show()
10 {
11 if(!$this->System->Modules['User']->CheckPermission('Finance', 'Manage')) return('Nemáte oprávnění');
12
13 if(array_key_exists('Operation', $_GET)) $Operation = $_GET['Operation']; else $Operation = '';
14 //$Operation = 'ImportOldData';
15 switch($Operation)
16 {
17 case 'Bills':
18 $Output = $this->BillManage();
19 break;
20 case 'ConvertPDFToFile':
21 $Output = $this->ConvertPDFDataToFiles();
22 break;
23 //case 'ImportOldData':
24 // $Output = $this->ImportOldData();
25 // break;
26 case 'NewDeviceForm':
27 $Output = $this->ShowNewDeviceForm();
28 break;
29 case 'NewDeviceInsert':
30 $Output = $this->ShowNewDeviceInsert();
31 break;
32 case 'NewDeviceHistoryForm':
33 $Output = $this->ShowNewDeviceHistoryForm();
34 break;
35 case 'NewDeviceHistoryInsert':
36 $Output = $this->ShowNewDeviceHistoryInsert();
37 break;
38 case 'NewPaymentInsert':
39 $Output = $this->ShowNewPaymentInsert();
40 break;
41 case 'NewPaymentForm':
42 $Output = $this->ShowNewPaymentForm();
43 break;
44 case 'NewInvoiceInsert':
45 $Output = $this->ShowNewInvoiceInsert();
46 break;
47 case 'NewInvoiceForm':
48 $Output = $this->ShowNewInvoiceForm();
49 break;
50 case 'Recalculate':
51 $Output = $this->System->Modules['Finance']->RecalculateSegmentParameters();
52 $Output .= $this->System->Modules['Finance']->RecalculateMemberPayment();
53 break;
54 case 'MonthlyPayment':
55 $Output = $this->ShowMonthlyPayment();
56 break;
57 default:
58 $Output = '<a href="?Operation=NewDeviceForm">Přidat nové zařízení</a><br />';
59 $Output .= '<a href="?Operation=NewDeviceHistoryForm">Přidat nový záznam historie zařízení</a><br />';
60 $Output .= '<a href="?Operation=NewPaymentForm">Přidat novou platbu</a><br />';
61 $Output .= '<a href="?Operation=NewInvoiceForm">Přidat novou fakturu</a><br />';
62 //$Output .= '<a href="?Operation=ImportOldData">Importovat stará data</a><br />';
63 $Output .= '<a href="?Operation=ConvertPDFToFile">Převést data z databáze do souborů</a><br />';
64 $Output .= '<a href="?Operation=Bills">Správa dokladů</a><br />';
65 //$Output .= '<a href="?Operation=ConvertData">Převést tabulky</a><br />';
66 $Output .= '<a href="?Operation=Recalculate">Přepočet financí</a><br />';
67 $Output .= '<a href="?Operation=MonthlyPayment">Měsíční vyúčtování</a><br />';
68 $Output .= '<a href="clenove.php">Seznam členů</a><br />';
69 $Output .= '<a href="zivnost.php">Živnost</a><br />';
70 }
71 return($Output);
72 }
73
74 function BillManage()
75 {
76 $Output = '';
77 if(array_key_exists('subject', $_GET))
78 {
79 $DbResult = $this->Database->select('FinanceBills', '*', 'Subject='.$_GET['subject']);
80 while($Item = $DbResult->fetch_array())
81 {
82 $Output .= '<a href="?Operation=Bills&amp;bill='.$Item['Id'].'">faktura '.$Item['Id'].'</a> <a href="?Operation=Bills&amp;billpdf='.$Item['Id'].'">Uložené PDF</a> <a href="?Operation=Bills&amp;billpdf2='.$Item['Id'].'">Generované PDF</a> <a href="?Operation=Bills&amp;regenerate='.$Item['Id'].'">Přegenerovat</a><br />';
83 }
84 } else
85 if(array_key_exists('billpdf', $_GET))
86 {
87 $Output .= $this->System->Modules['Bill']->ShowStoredBill($_GET['billpdf']);
88 } else
89 if(array_key_exists('billpdf2', $_GET))
90 {
91 $Output .= $this->System->Modules['Bill']->ShowGeneratedBill($_GET['billpdf2']);
92 } else
93 if(array_key_exists('regenerate', $_GET))
94 {
95 $Output .= $this->System->Modules['Bill']->RegeneratePDF($_GET['regenerate']);
96 } else
97 if(array_key_exists('bill', $_GET))
98 {
99 $Output .= $this->System->Modules['Bill']->GenerateBill($_GET['bill']);
100 } else
101 if(array_key_exists('generate', $_GET))
102 {
103 $Output .= $this->System->Modules['Bill']->CreateBill(1, array(array('Description' => 'Poplatek za připojení k síti', 'Price' => 1000, 'Quantity' => 1)), time(), time());
104 } else
105 {
106 //ShowHeader('Faktury', 'Faktury');
107 $Output .= 'Faktury:<br />';
108 $DbResult = $this->Database->select('Subject', '*', '1 ORDER BY Name');
109 while($Subject = $DbResult->fetch_array())
110 {
111 $Output .= '<a href="?Operation=Bills&amp;subject='.$Subject['Id'].'">'.$Subject['Name'].'</a><br />';
112 }
113 //ShowFooter();
114 }
115 return($Output);
116 }
117
118 function CheckAdvancesAndLiabilities($Subject)
119 {
120 global $LastInsertTime;
121
122 do {
123 $DbResult = $this->Database->select('FinanceAdvances', 'SUM(Value)', 'Subject='.$Subject.' AND Direction = "In"');
124 $DbRow = $DbResult->fetch_array();
125 $Advances = $DbRow[0];
126 $DbResult = $this->Database->select('FinanceClaimsLiabilities', '*', 'Subject='.$Subject.' AND TimePayment IS NULL AND Value > 0 ORDER BY TimeCreation LIMIT 1');
127 //echo($Database->error);
128 if($DbResult->num_rows > 0)
129 {
130 $OpenedClaim = $DbResult->fetch_array();
131 if($Advances > $OpenedClaim['Value'])
132 {
133 $this->Database->update('FinanceClaimsLiabilities', 'Id='.$OpenedClaim['Id'], array('TimePayment' => TimeToMysqlDateTime($LastInsertTime)));
134 $this->Database->insert('FinanceAdvances', array('Subject' => $OpenedClaim['Subject'], 'Value' => -$OpenedClaim['Value'], 'TimeCreation' => TimeToMysqlDateTime($LastInsertTime), 'CashFlowId' => $OpenedClaim['Id'], 'Direction' => 'In'));
135 //echo($Database->LastQuery);
136 } else break;
137 } else break;
138 } while(($Advances > $OpenedClaim['Value']));
139 }
140
141 function InsertLiability($Subject, $Value, $Time, $BillId, $Text, $TimePayment = '')
142 {
143 global $LastInsertTime;
144
145 if($TimePayment != '') $TimePayment = TimeToMysqlDateTime($TimePayment);
146 $this->Database->insert('FinanceClaimsLiabilities', array('Text' => $Text, 'Subject' => $Subject, 'TimeCreation' => TimeToMysqlDateTime($Time), 'TimeDue' => TimeToMysqlDateTime($Time + 3600*24*15), 'TimePayment' => $TimePayment, 'Value' => $Value, 'Bill' => $BillId));
147 $Output = '.'; //$this->Database->LastQuery.'<br />';
148 $LastInsertTime = $Time;
149 $this->CheckAdvancesAndLiabilities($Subject);
150 return($Output);
151 }
152
153 function InsertMoney($Subject, $Value, $Cash, $Taxable, $Time, $Text)
154 {
155 global $LastInsertTime;
156
157 $this->Database->insert('FinanceCashFlow', array('Text' => $Text, 'Subject' => $Subject, 'Cash' => $Cash, 'Value' => $Value, 'Time' => TimeToMysqlDateTime($Time), 'Taxable' => $Taxable));
158 if($Value >= 0)
159 {
160 $this->Database->insert('FinanceAdvances', array('Subject' => $Subject, 'Value' => $Value, 'TimeCreation' => TimeToMysqlDateTime($Time), 'CashFlowId' => $this->Database->insert_id, 'Direction' => 'In'));
161 }
162 $LastInsertTime = $Time;
163 $this->CheckAdvancesAndLiabilities($Subject);
164 }
165
166 function ShowNewDeviceForm()
167 {
168 $Form = new Form('NewNetworkDevice');
169 $Form->OnSubmit = '?Operation=NewDeviceInsert';
170 $Output = $Form->ShowEditForm();
171 return($Output);
172 }
173
174 function ShowNewDeviceInsert()
175 {
176 $Form = new Form('NewNetworkDevice');
177 $Form->LoadValuesFromForm();
178 $Form->SaveValuesToDatabase(0);
179 $Output = $this->SystemMessage('Finance', 'Zařízení vloženo.');
180 $this->System->Modules['Log']->NewRecord('Finance', 'NewDeviceInserted');
181 return($Output);
182 }
183
184 function ShowNewDeviceHistoryForm()
185 {
186 $Form = new Form('NewNetworkDeviceHistory');
187 $Form->OnSubmit = '?Operation=NewDeviceHistoryInsert';
188 $Output = $Form->ShowEditForm();
189 return($Output);
190 }
191
192 function ShowNewDeviceHistoryInsert()
193 {
194 $Form = new Form('NewNetworkDeviceHistory');
195 $Form->LoadValuesFromForm();
196 $Form->SaveValuesToDatabase(0);
197 $Output = $this->SystemMessage('Finance', 'Záznam historie zařízení vložen.');
198 $this->System->Modules['Log']->NewRecord('Finance', 'NewDeviceHistoryInserted');
199 return($Output);
200 }
201
202 function ShowNewPaymentForm()
203 {
204 $Form = new Form('NewPayment');
205 $Form->OnSubmit = '?Operation=NewPaymentInsert';
206 $Output = $Form->ShowEditForm();
207 return($Output);
208 }
209
210 function ShowNewPaymentInsert()
211 {
212 $Form = new Form('NewPayment');
213 $Form->LoadValuesFromForm();
214 $this->InsertMoney($Form->Values['Subject'], $Form->Values['Value'], $Form->Values['Cash'], $Form->Values['Taxable'], $Form->Values['Time'], $Form->Values['Text']);
215 $Output = $this->SystemMessage('Finance', 'Platba vložena.');
216 $this->System->Modules['Log']->NewRecord('Finance', 'NewPaymentInserted');
217 return($Output);
218 }
219
220 function ShowNewInvoiceForm()
221 {
222 $Form = new Form('NewInvoice');
223 $Form->OnSubmit = '?Operation=NewInvoiceInsert';
224 $Output = $Form->ShowEditForm();
225 return($Output);
226 }
227
228 function ShowNewInvoiceInsert()
229 {
230 $Form = new Form('NewInvoice');
231 $Form->LoadvaluesFromForm();
232 //print_r($Form->Values);
233 //$this->InsertLiability($Form->Values['Subject'], $Form->Values['Value'], $Form->Values['Time'], $BillId, $Text, $TimePayment = '')
234 $Output = $this->SystemMessage('Finance', 'Faktura vložena..');
235 $this->System->Modules['Log']->NewRecord('Finance', 'NewInvoiceInserted');
236 return($Output);
237 }
238
239 /*
240 function ImportOldData()
241 {
242 $Output = '';
243 $this->Database->query('TRUNCATE TABLE FinanceCashFlow');
244 $this->Database->query('TRUNCATE TABLE FinanceClaimsLiabilities');
245 $this->Database->query('TRUNCATE TABLE FinanceAdvances');
246 $this->Database->query('TRUNCATE TABLE FinanceSmallAssets');
247
248 // Move time of device price transformation
249 $this->Database->query('UPDATE finance_operations SET date="2007-11-30" WHERE comment = "Jednorázový poplatek za společné zařízení"');
250
251 // Transfer finance before era
252 $DbResult = $this->Database->query('SELECT * FROM Subject');
253 while($Subject = $DbResult->fetch_assoc())
254 {
255 $DbResult2 = $this->Database->query('SELECT SUM(money) FROM finance_operations WHERE user='.$Subject['Id'].' AND date < "2007-12-01"');
256 $Row = $DbResult2->fetch_row();
257 $TotalAdvance = $Row[0];
258 //echo($User['second_name'].' '.$User['first_name'].' '.$TotalAdvance.'<br />');
259 // Záloha
260 if($TotalAdvance > 0)
261 {
262 $this->InsertMoney($Subject['Id'], $TotalAdvance, 0, 1, mktime(0, 0, 0, 12, 1, 2007), 'Přijatá záloha (z období před daňovou evidencí)');
263 }
264 // Dluh
265 if($TotalAdvance < 0)
266 {
267 $this->InsertLiability($Subject['Id'], (-$Row[0]), mktime(0, 0, 0, 12, 1, 2007), 0, 'Připojení k síti (z období před daňovou evidencí)');
268 //echo($Database->LastQuery.'<br />');
269 }
270 }
271
272 // Transfer finance after era
273 // $DbResult = $Database->query('SELECT * FROM users WHERE role=2');
274 // while($User = $DbResult->fetch_array())
275 // {
276 $DbResult2 = $this->Database->query('SELECT finance_operations.* FROM finance_operations JOIN Subject ON Subject.Id = finance_operations.user WHERE finance_operations.date >= "2007-12-01"');
277 while($Operation = $DbResult2->fetch_assoc())
278 {
279 //echo($Operation['comment'].'<br />');
280 if(substr($Operation['comment'], 0, 19) == 'Poplatek za měsíc')
281 {
282 $this->InsertLiability($Operation['user'], -$Operation['money'], MysqlDateToTime($Operation['date']), $Operation['bill_id'], 'Připojení k síti');
283 $Output .= $Operation['user'].' '.$Operation['money'].' Připojení k síti<br />';
284 } else
285 if($Operation['comment'] == 'Vklad')
286 {
287 $this->InsertMoney($Operation['user'], $Operation['money'], 0, 1, MysqlDateToTime($Operation['date']), 'Přijatá záloha');
288 //echo($Operation['user'].' '.$Operation['money'].' Přijatá záloha<br />');
289 } else
290 if($Operation['comment'] == 'Internet')
291 {
292 $this->InsertLiability($Operation['user'], $Operation['money'], MysqlDateToTime($Operation['date']), $Operation['bill_id'], 'Měsíční paušál za Internet', MysqlDateToTime($Operation['date']));
293 $this->InsertMoney($Operation['user'], $Operation['money'], 0, 1, MysqlDateToTime($Operation['date']), 'Měsíční paušál za Internet');
294 } else
295 if($Operation['comment'] == 'Sociální pojištění')
296 {
297 $this->InsertLiability($Operation['user'], $Operation['money'], MysqlDateToTime($Operation['date']), $Operation['bill_id'], $Operation['comment'], MysqlDateToTime($Operation['date']));
298 $this->InsertMoney($Operation['user'], $Operation['money'], 0, 1, MysqlDateToTime($Operation['date']), $Operation['comment']);
299 } else
300 {
301 $DbResult = $this->Database->query('SELECT Id FROM Subject WHERE Id='.$Operation['user']);
302 while($User = $DbResult->fetch_array())
303 $this->InsertLiability($Operation['user'], -$Operation['money'], MysqlDateToTime($Operation['date']), $Operation['bill_id'], $Operation['comment'], MysqlDateToTime($Operation['date']));
304 $Output .= $Operation['user'].' '.$Operation['comment'].' '.MysqlDateToTime($Operation['date']).'<br />';
305
306// if(($Operation['role'] == 2))
307 // {
308 $Output .= 'A';
309 $this->Database->insert('FinanceAdvances', array('Subject' => $Operation['user'], 'Value' => $Operation['money'], 'TimeCreation' => MysqlDateToTime($Operation['date']), 'CashFlowId' => 0, 'Direction' => 'In'));
310 $this->CheckAdvancesAndLiabilities($Operation['user']);
311 //} else InsertMoney($Operation['user'], $Operation['money'], 0, 1, MysqlDateToTime($Operation['date']), $Operation['comment']);
312 }
313 }
314 // }
315
316 // Import small asset
317 $Subject = 1;
318
319 // Převod pro minulý rok
320 $Time = mktime(0, 0, 0, 12, 10, 2007);
321 $DbResult = $this->Database->query('SELECT * FROM network_devices WHERE used <> 0 AND date < "'.TimeToMysqlDateTime($Time).'" AND (id <> 73)');
322 $Items = array();
323 $TotalPrice = 0;
324 while($Device = $DbResult->fetch_array())
325 {
326 //$Database->insert('FinanceSmallAsset', array());
327 $Items[] = array('Description' => $Device['name'], 'Quantity' => $Device['count'], 'Price' => 0); //$Device['price']);
328 $LastId = $Device['id'];
329 $TotalPrice += $Device['price'] * $Device['count'];
330 if($TotalPrice > 55000) break;
331 }
332 //print_r($Items);
333 $BillId = $this->System->Modules['Bill']->CreateBill($Subject, $Items, $Time, $Time);
334 $DbResult = $this->Database->query('UPDATE network_devices SET TimeEnlistment = "'.TimeToMysqlDateTime($Time).'" WHERE used <> 0 AND date < "'.TimeToMysqlDateTime($Time).'" AND (id <> 73) AND (id <= '.$LastId.')');
335 //echo($Database->error);
336 $this->InsertLiability($Subject, 0, $Time, $BillId, 'Nákup infrastruktury', $Time);
337 //InsertMoney($Subject, 0, 0, 1, $Time, 'Nákup infrastruktury');
338
339 //echo($LastId);
340
341 // Převod tento rok
342 $Time = mktime(0, 0, 0, 1, 14, 2008);
343 $DbResult = $this->Database->query('SELECT * FROM network_devices WHERE (used <> 0) AND (id > '.$LastId.') AND date < "'.TimeToMysqlDateTime($Time).'"');
344 $Items = array();
345 $TotalPrice = 0;
346 while($Device = $DbResult->fetch_array())
347 {
348 // $Database->insert('FinanceSmallAsset', array());
349 $Items[] = array('Description' => $Device['name'], 'Quantity' => $Device['count'], 'Price' => 0); //$Device['price']);
350 $TotalPrice += $Device['price'] * $Device['count'];
351 }
352 //print_r($Items);
353 $BillId = $this->System->Modules['Bill']->CreateBill($Subject, $Items, $Time, $Time);
354 $DbResult = $this->Database->query('UPDATE network_devices SET TimeEnlistment = "'.TimeToMysqlDateTime($Time).'" WHERE used <> 0 AND date < "'.TimeToMysqlDateTime($Time).'" AND (id > '.$LastId.')');
355 $this->InsertLiability($Subject, 0, $Time, $BillId, 'Nákup infrastruktury', $Time);
356 //InsertMoney($Subject, 0, 0, 1, $Time, 'Nákup infrastruktury');
357
358
359 // Make absolute value in monthly overall
360 //$Database->query('UPDATE finance_monthly_overall SET total_paid = ABS(total_paid)');
361 //$Database->query('UPDATE finance_monthly_overall SET member_count = (SELECT COUNT(*) FROM users WHERE users.role=2 AND users.membership_date < finance_monthly_overall.date)');
362 return($Output);
363 }
364 */
365
366 function ConvertPDFDataToFiles()
367 {
368 $DbResult = $this->Database->query('SELECT * FROM FinanceBills');
369 while($Bill = $DbResult->fetch_assoc())
370 {
371 file_put_contents('doklady/doklad_'.$Bill['id'].'.pdf', $Bill['pdf']);
372 echo($Bill['id'].',');
373 }
374 }
375
376 function CheckPDFFiles()
377 {
378 global $InvoiceGenerator;
379
380 $DbResult = $this->Database->query('SELECT * FROM FinanceBills');
381 while($Bill = $DbResult->fetch_assoc())
382 {
383 file_put_contents('doklady2/doklad-'.$Bill['id'].'.pdf', file_get_contents('doklady/doklad-'.$Bill['id'].'.pdf'));
384 //echo($InvoiceGenerator->HasPDFFile($Bill['id']).',');
385 }
386 }
387
388 /*
389 function ConvertData()
390 {
391 $Finance = $this->System->Modules['Finance'];
392 $Output = '';
393 $this->Database->query('TRUNCATE TABLE FinanceOperation');
394 $this->Database->query('TRUNCATE TABLE FinanceClaimsLiabilities');
395
396 // Move time of device price transformation
397 $this->Database->query('UPDATE finance_operations SET date="2007-11-30" WHERE comment = "Jednorázový poplatek za společné zařízení"');
398
399 // Transform old operations
400 $DbResult = $this->Database->query('SELECT * FROM finance_operations WHERE finance_operations.date >= "2007-12-01" AND (Source = '.$Finance->ExternalSubject.')');
401 while($DbRow = $DbResult->fetch_assoc())
402 {
403 $this->Database->insert('FinanceOperation', array('Id' => $DbRow['id'], 'Subject' => $DbRow['Destination'], 'Cash' => $DbRow['cash'], 'Value' => abs($DbRow['money']), 'Time' => $DbRow['date'], 'BillCode' => $DbRow['BillCode'], 'Taxable' => $DbRow['Taxable'], 'Text' => $DbRow['comment'], 'Bill' => $DbRow['bill_id']));
404 $Output .= '.';
405 }
406 $DbResult = $this->Database->query('SELECT * FROM finance_operations WHERE finance_operations.date >= "2007-12-01" AND (Destination = '.$Finance->ExternalSubject.')');
407 while($DbRow = $DbResult->fetch_assoc())
408 {
409 $this->Database->insert('FinanceOperation', array('Id' => $DbRow['id'], 'Subject' => $DbRow['Source'], 'Cash' => $DbRow['cash'], 'Value' => -abs($DbRow['money']), 'Time' => $DbRow['date'], 'BillCode' => $DbRow['BillCode'], 'Taxable' => $DbRow['Taxable'], 'Text' => $DbRow['comment'], 'Bill' => $DbRow['bill_id']));
410 $Output .= '.';
411 }
412
413 // Transform old invoices
414 $DbResult = $this->Database->query('SELECT * FROM finance_operations WHERE finance_operations.date >= "2007-12-01" AND (Source = '.$Finance->MainSubject.')');
415 while($DbRow = $DbResult->fetch_assoc())
416 {
417 $this->Database->insert('FinanceClaimsLiabilities', array('Id' => $DbRow['id'], 'Subject' => $DbRow['Destination'], 'Value' => -abs($DbRow['money']), 'TimeCreation' => $DbRow['date'], 'BillCode' => $DbRow['BillCode'], 'Text' => $DbRow['comment'], 'Bill' => $DbRow['bill_id']));
418 $Output .= 'FV '.$DbRow['comment'].'<br />';
419 }
420 $DbResult = $this->Database->query('SELECT * FROM finance_operations WHERE finance_operations.date >= "2007-12-01" AND (Destination = '.$Finance->MainSubject.')');
421 while($DbRow = $DbResult->fetch_assoc())
422 {
423 $this->Database->insert('FinanceClaimsLiabilities', array('Id' => $DbRow['id'], 'Subject' => $DbRow['Source'], 'Value' => abs($DbRow['money']), 'TimeCreation' => $DbRow['date'], 'BillCode' => $DbRow['BillCode'], 'Text' => $DbRow['comment'], 'Bill' => $DbRow['bill_id']));
424 $Output .= 'FP '.$DbRow['comment'].'<br />';
425 }
426
427 // Transfer finance before era
428 $DbResult = $this->Database->query('SELECT * FROM Subject');
429 while($Subject = $DbResult->fetch_assoc())
430 {
431 $DbResult2 = $this->Database->query('SELECT SUM(money) as money FROM finance_operations WHERE user='.$Subject['Id'].' AND date < "2007-12-01"');
432 $DbRow2 = $DbResult2->fetch_assoc();
433
434 $DbRow2['date'] = TimeToMysqlDateTime(mktime(0, 0, 0, 12, 1, 2007));
435 if($DbRow2['money'] > 0)
436 {
437 $Comment = 'Přijatá záloha (z období před daňovou evidencí)';
438 $this->Database->insert('FinanceOperation', array('Subject' => $Subject['Id'], 'Cash' => 0, 'Value' => abs($DbRow2['money']), 'Time' => $DbRow2['date'], 'Taxable' => 1, 'Text' => $Comment));
439} else
440 {
441 $Comment = 'Připojení k síti (z období před daňovou evidencí)';
442 $this->Database->insert('FinanceClaimsLiabilities', array('Subject' => $Subject['Id'], 'Value' => abs($DbRow2['money']), 'TimeCreation' => $DbRow2['date'], 'Text' => $Comment));
443 }
444 $Output .= '#';
445 }
446
447 return($Output);
448 }
449 */
450
451
452 function GetBillingPeriod($Period)
453 {
454 $MonthCount = $this->System->Modules['Finance']->BillingPeriods[$Period]['MonthCount'];
455 $PeriodFrom = mktime(0, 0, 0, date('n'), 1, date('Y'));
456 $PeriodTo = mktime(0, 0, 0, date('n') + $MonthCount - 1, date('t', mktime(0, 0, 0, date('n') + $MonthCount - 1, 1, date('Y'))) , date('Y'));
457 return(array('From' => $PeriodFrom, 'To' => $PeriodTo, 'MonthCount' => $MonthCount));
458 }
459
460 function ShowMonthlyPayment()
461 {
462 if(!$this->System->Modules['User']->CheckPermission('Finance', 'Manage')) return('Nemáte oprávnění');
463 $Output = '';
464
465 $Finance = &$this->System->Modules['Finance'];
466
467 // Načti poslední měsíční přehled a nastavení
468 $DbResult = $this->Database->select('finance_monthly_overall', '*', '1 ORDER BY date DESC LIMIT 1');
469 $Overall = $DbResult->fetch_array();
470
471 $Output -= 'Datum: '.date('j.n.Y').'<br />';
472
473 $Datum = explode('-', $Overall['date']);
474 $Mesic = date('m') + 0;
475
476 $Output .= $Finance->RecalculateSegmentParameters();
477 $Output .= $Finance->RecalculateTariffs();
478 //$InvoiceGenerator->CustomGenerate();
479 $Output .= $Finance->RecalculateMemberPayment();
480
481 // Celkovy prehled
482 $DbResult = $this->Database->query('SELECT SUM(price) FROM network_devices WHERE used = 1');
483 $Row = $DbResult->fetch_row();
484 $TotalDeviceCost = $Row[0];
485 $Output .= 'Celková cena zařízení: Zařízení('.$TotalDeviceCost;
486
487 $DbResult = $this->Database->query('SELECT SUM(price) FROM network_segments');
488 $Row = $DbResult->fetch_row();
489 $TotalSegmentDeviceCost = $Row[0];
490 $Output .= '), Segmenty('.$TotalSegmentDeviceCost;
491
492 $DbResult = $this->Database->query('SELECT SUM(NetworkDevice) FROM MemberPayment');
493 $Row = $DbResult->fetch_row();
494 $TotalMemberDeviceCost = $Row[0];
495 $Output .= '), Členové('.$TotalMemberDeviceCost.')<br />';
496
497 $DbResult = $this->Database->query('SELECT SUM(Cash) FROM MemberPayment');
498 $Row = $DbResult->fetch_row();
499 $TotalMemberCash = $Row[0];
500 $Output .= 'Stav pokladny: Členové('.$TotalMemberCash.')';
501
502 $DbResult = $this->Database->query('SELECT SUM(consumption) FROM network_devices WHERE used=1');
503 $Row = $DbResult->fetch_row();
504 $TotalConsumption = $Row[0];
505 $TotalConsumptionCost = $Finance->W2Kc($TotalConsumption);
506
507 $SpravaCelkem = $Finance->Sprava * $Finance->SpravaUsers;
508 $Output .= 'Kontrola placení (Zaplaceno-Sprava-Internet): '.$Finance->TotalPaid.'-'.$SpravaCelkem.'-'.$Finance->Internet.'='.($Finance->TotalPaid - $SpravaCelkem - $Finance->Internet).'<br />';
509
510 // Zkontrolovat odečtení měsíčního poplatku
511 //$Mesic = '1';
512 $Output .= 'Kontrola odečtení poplatků: Poslední měsíc-'.round($Datum[1]).' Aktuální měsíc-'.$Mesic.'<br />';
513 if($Mesic != $Datum[1])
514 {
515 $Output .= 'Odečítám měsíční poplatek...<br />';
516
517 // Generuj účetní položky
518 $DbResult = $this->Database->query('SELECT Member.*, MemberPayment.MonthlyTotal, UNIX_TIMESTAMP(Member.BillingPeriodLastDate), Subject.Name AS SubjectName FROM MemberPayment JOIN Member ON Member.Id=MemberPayment.Member JOIN Subject ON Subject.Id=Member.Subject');
519 while($Member = $DbResult->fetch_array())
520 {
521 $Output .= $Member['SubjectName'].': ';
522 $Period = $this->GetBillingPeriod($Member['BillingPeriodNext']);
523 if($Period['From'] > $Member['UNIX_TIMESTAMP(Member.BillingPeriodLastDate)'])
524 {
525 $this->Database->update('Member', 'Id='.$Member['Id'], array('BillingPeriod' => $Member['BillingPeriodNext'], 'InternetTariffCurrentMonth' => $Member['InternetTariffNextMonth']));
526 $Member['BillingPeriod'] = $Member['BillingPeriodNext'];
527 }
528 $Period = $this->GetBillingPeriod($Member['BillingPeriod']);
529 $PayPerPeriod = $Member['MonthlyTotal'] * $Period['MonthCount'];
530 if(($Period['From'] > $Member['UNIX_TIMESTAMP(Member.BillingPeriodLastDate)']) and ($Member['InternetTariffCurrentMonth'] != 6) and ($PayPerPeriod > 0))
531 {
532 //echo($Mesic.'%'.$MonthCount.'='.($Mesic % $MonthCount).' ');
533 $TimePeriodText = date('j.n.Y', $Period['From']).' - '.date('j.n.Y', $Period['To']);
534 $Output .= $TimePeriodText.': '.$Member['MonthlyTotal'].' * '.$Period['MonthCount'].' = '.$PayPerPeriod.'<br />';
535 $BillCode = $Finance->GetNextDocumentLineNumber(6); // Faktury vydané
536 $BillId = $this->System->Modules['Bill']->CreateBill($Member['Subject'], array(array('Description' => 'Připojení k síti', 'Price' => $PayPerPeriod, 'Quantity' => 1)), $Period['From'], $Period['To'], $BillCode);
537 $this->Database->insert('FinanceClaimsLiabilities', array('Value' => $PayPerPeriod, 'Subject' => $Member['Subject'], 'TimeCreation' => 'NOW()', 'Text' => 'Připojení k síti za období '.$TimePeriodText, 'Bill' => $BillId, 'BillCode' => $BillCode));
538 $this->Database->update('Member', 'Id='.$Member['Id'], array('BillingPeriodLastDate' => TimeToMysqlDateTime($Period['To'])));
539 } else $Output .= '<br />';
540 }
541
542 // Update finance charge
543 $Output .= 'Měním aktuální parametry sítě...<br>';
544 $this->Database->delete('finance_charge', 'period = 0');
545 $DbResult = $this->Database->select('finance_charge', '*', 'period = 1');
546 $Charge = $DbResult->fetch_assoc();
547 //print_r($Charge);
548 $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']));
549
550 $Output .= 'Přidávám měsíční přehled...<br />';
551 $this->Database->insert('finance_monthly_overall', array('date' => 'NOW()', 'money' => $Finance->Internet, 'kWh' => $Finance->kWh, 'administration' => $Finance->Sprava, 'administration_total' => $SpravaCelkem, 'consumption_total' => $TotalConsumptionCost, 'total_paid' => $Finance->TotalPaid, 'BaseTariffPrice' => $Charge['BaseTariffPrice'], 'TopTariffPrice' => $Charge['TopTariffPrice'], 'member_count' => $Finance->InternetUsers));
552
553 $Output .= 'Měním aktuální tarify....<br>';
554 // Update tarrifs
555 $this->Database->delete('finance_tariffs', 'period=0');
556 $DbResult = $this->Database->select('finance_tariffs', '*', 'period = 1');
557 while($Tariff = $DbResult->fetch_array())
558 {
559 $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']));
560 }
561
562 $Output .= $Finance->RecalculateTariffs();
563 $Finance->RecalculateMemberPayment();
564 //CreateMonthlyOverallBill();
565 //$Finance->RecalculateUsersFinance();
566
567 // Restart traffic shaping
568 $this->Database->update('services_restart', 'id = 3', array('changed' => 1));
569 }
570 return($Output);
571 }
572}
573
574$System->AddModule(new FinanceManagePage());
575$System->Modules['FinanceManagePage']->GetOutput();
576
577?>
Note: See TracBrowser for help on using the repository browser.