source: trunk/finance/manage.php@ 295

Last change on this file since 295 was 295, checked in by george, 15 years ago
  • Přidáno: Funkce pro zpožděné generování chybějících dokladů.
File size: 32.7 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 case 'GenerateBills':
58 $Output = $this->GenerateBills();
59 break;
60 default:
61 $Output = '<a href="?Operation=NewDeviceForm">Přidat nové zařízení</a><br />';
62 $Output .= '<a href="?Operation=NewDeviceHistoryForm">Přidat nový záznam historie zařízení</a><br />';
63 $Output .= '<a href="?Operation=NewPaymentForm">Přidat novou platbu</a><br />';
64 $Output .= '<a href="?Operation=NewInvoiceForm">Přidat novou fakturu</a><br />';
65 //$Output .= '<a href="?Operation=ImportOldData">Importovat stará data</a><br />';
66 $Output .= '<a href="?Operation=ConvertPDFToFile">Převést data z databáze do souborů</a><br />';
67 $Output .= '<a href="?Operation=Bills">Správa dokladů</a><br />';
68 //$Output .= '<a href="?Operation=ConvertData">Převést tabulky</a><br />';
69 $Output .= '<a href="?Operation=Recalculate">Přepočet financí</a><br />';
70 $Output .= '<a href="?Operation=MonthlyPayment">Měsíční vyúčtování</a><br />';
71 $Output .= '<a href="clenove.php">Seznam členů</a><br />';
72 $Output .= '<a href="zivnost.php">Živnost</a><br />';
73 $Output .= '<a href="?Operation=GenerateBills">Generovat chybějící doklady</a><br />';
74 }
75 return($Output);
76 }
77
78 function BillManage()
79 {
80 $Output = '';
81 if(array_key_exists('subject', $_GET))
82 {
83 $DbResult = $this->Database->select('FinanceBills', '*', 'Subject='.$_GET['subject']);
84 while($Item = $DbResult->fetch_array())
85 {
86 $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 />';
87 }
88 } else
89 if(array_key_exists('billpdf', $_GET))
90 {
91 $Output .= $this->System->Modules['Bill']->ShowStoredBill($_GET['billpdf']);
92 } else
93 if(array_key_exists('billpdf2', $_GET))
94 {
95 $Output .= $this->System->Modules['Bill']->ShowGeneratedBill($_GET['billpdf2']);
96 } else
97 if(array_key_exists('regenerate', $_GET))
98 {
99 $Output .= $this->System->Modules['Bill']->RegeneratePDF($_GET['regenerate']);
100 } else
101 if(array_key_exists('bill', $_GET))
102 {
103 $Output .= $this->System->Modules['Bill']->GenerateBill($_GET['bill']);
104 } else
105 if(array_key_exists('generate', $_GET))
106 {
107 $Output .= $this->System->Modules['Bill']->CreateBill(1, array(array('Description' => 'Poplatek za připojení k síti', 'Price' => 1000, 'Quantity' => 1)), time(), time());
108 } else
109 {
110 //ShowHeader('Faktury', 'Faktury');
111 $Output .= 'Faktury:<br />';
112 $DbResult = $this->Database->select('Subject', '*', '1 ORDER BY Name');
113 while($Subject = $DbResult->fetch_array())
114 {
115 $Output .= '<a href="?Operation=Bills&amp;subject='.$Subject['Id'].'">'.$Subject['Name'].'</a><br />';
116 }
117 //ShowFooter();
118 }
119 return($Output);
120 }
121
122 function CheckAdvancesAndLiabilities($Subject)
123 {
124 global $LastInsertTime;
125
126 do {
127 $DbResult = $this->Database->select('FinanceAdvances', 'SUM(Value)', 'Subject='.$Subject.' AND Direction = "In"');
128 $DbRow = $DbResult->fetch_array();
129 $Advances = $DbRow[0];
130 $DbResult = $this->Database->select('FinanceClaimsLiabilities', '*', 'Subject='.$Subject.' AND TimePayment IS NULL AND Value > 0 ORDER BY TimeCreation LIMIT 1');
131 //echo($Database->error);
132 if($DbResult->num_rows > 0)
133 {
134 $OpenedClaim = $DbResult->fetch_array();
135 if($Advances > $OpenedClaim['Value'])
136 {
137 $this->Database->update('FinanceClaimsLiabilities', 'Id='.$OpenedClaim['Id'], array('TimePayment' => TimeToMysqlDateTime($LastInsertTime)));
138 $this->Database->insert('FinanceAdvances', array('Subject' => $OpenedClaim['Subject'], 'Value' => -$OpenedClaim['Value'], 'TimeCreation' => TimeToMysqlDateTime($LastInsertTime), 'CashFlowId' => $OpenedClaim['Id'], 'Direction' => 'In'));
139 //echo($Database->LastQuery);
140 } else break;
141 } else break;
142 } while(($Advances > $OpenedClaim['Value']));
143 }
144
145 function InsertLiability($Subject, $Value, $Time, $BillId, $Text, $TimePayment = '')
146 {
147 global $LastInsertTime;
148
149 if($TimePayment != '') $TimePayment = TimeToMysqlDateTime($TimePayment);
150 $this->Database->insert('FinanceClaimsLiabilities', array('Text' => $Text, 'Subject' => $Subject, 'TimeCreation' => TimeToMysqlDateTime($Time), 'TimeDue' => TimeToMysqlDateTime($Time + 3600*24*15), 'TimePayment' => $TimePayment, 'Value' => $Value, 'Bill' => $BillId));
151 $Output = '.'; //$this->Database->LastQuery.'<br />';
152 $LastInsertTime = $Time;
153 $this->CheckAdvancesAndLiabilities($Subject);
154 return($Output);
155 }
156
157 function InsertMoney($Subject, $Value, $Cash, $Taxable, $Time, $Text)
158 {
159 global $LastInsertTime;
160
161 $this->Database->insert('FinanceCashFlow', array('Text' => $Text, 'Subject' => $Subject, 'Cash' => $Cash, 'Value' => $Value, 'Time' => TimeToMysqlDateTime($Time), 'Taxable' => $Taxable));
162 if($Value >= 0)
163 {
164 $this->Database->insert('FinanceAdvances', array('Subject' => $Subject, 'Value' => $Value, 'TimeCreation' => TimeToMysqlDateTime($Time), 'CashFlowId' => $this->Database->insert_id, 'Direction' => 'In'));
165 }
166 $LastInsertTime = $Time;
167 $this->CheckAdvancesAndLiabilities($Subject);
168 }
169
170 function ShowNewDeviceForm()
171 {
172 $Form = new Form('NewNetworkDevice');
173 $Form->OnSubmit = '?Operation=NewDeviceInsert';
174 $Output = $Form->ShowEditForm();
175 return($Output);
176 }
177
178 function ShowNewDeviceInsert()
179 {
180 $Form = new Form('NewNetworkDevice');
181 $Form->LoadValuesFromForm();
182 $Form->SaveValuesToDatabase(0);
183 $Output = $this->SystemMessage('Finance', 'Zařízení vloženo.');
184 $this->System->Modules['Log']->NewRecord('Finance', 'NewDeviceInserted');
185 return($Output);
186 }
187
188 function ShowNewDeviceHistoryForm()
189 {
190 $Form = new Form('NewNetworkDeviceHistory');
191 $Form->OnSubmit = '?Operation=NewDeviceHistoryInsert';
192 $Output = $Form->ShowEditForm();
193 return($Output);
194 }
195
196 function ShowNewDeviceHistoryInsert()
197 {
198 $Form = new Form('NewNetworkDeviceHistory');
199 $Form->LoadValuesFromForm();
200 $Form->SaveValuesToDatabase(0);
201 $Output = $this->SystemMessage('Finance', 'Záznam historie zařízení vložen.');
202 $this->System->Modules['Log']->NewRecord('Finance', 'NewDeviceHistoryInserted');
203 return($Output);
204 }
205
206 function ShowNewPaymentForm()
207 {
208 $Form = new Form('NewPayment');
209 $Form->OnSubmit = '?Operation=NewPaymentInsert';
210 $Output = $Form->ShowEditForm();
211 return($Output);
212 }
213
214 function ShowNewPaymentInsert()
215 {
216 $Form = new Form('NewPayment');
217 $Form->LoadValuesFromForm();
218 $this->InsertMoney($Form->Values['Subject'], $Form->Values['Value'], $Form->Values['Cash'], $Form->Values['Taxable'], $Form->Values['Time'], $Form->Values['Text']);
219 $Output = $this->SystemMessage('Finance', 'Platba vložena.');
220 $this->System->Modules['Log']->NewRecord('Finance', 'NewPaymentInserted');
221 return($Output);
222 }
223
224 function ShowNewInvoiceForm()
225 {
226 $Form = new Form('NewInvoice');
227 $Form->OnSubmit = '?Operation=NewInvoiceInsert';
228 $Output = $Form->ShowEditForm();
229 return($Output);
230 }
231
232 function ShowNewInvoiceInsert()
233 {
234 $Form = new Form('NewInvoice');
235 $Form->LoadvaluesFromForm();
236 //print_r($Form->Values);
237 //$this->InsertLiability($Form->Values['Subject'], $Form->Values['Value'], $Form->Values['Time'], $BillId, $Text, $TimePayment = '')
238 $Output = $this->SystemMessage('Finance', 'Faktura vložena..');
239 $this->System->Modules['Log']->NewRecord('Finance', 'NewInvoiceInserted');
240 return($Output);
241 }
242
243 /*
244 function ImportOldData()
245 {
246 $Output = '';
247 $this->Database->query('TRUNCATE TABLE FinanceCashFlow');
248 $this->Database->query('TRUNCATE TABLE FinanceClaimsLiabilities');
249 $this->Database->query('TRUNCATE TABLE FinanceAdvances');
250 $this->Database->query('TRUNCATE TABLE FinanceSmallAssets');
251
252 // Move time of device price transformation
253 $this->Database->query('UPDATE finance_operations SET date="2007-11-30" WHERE comment = "Jednorázový poplatek za společné zařízení"');
254
255 // Transfer finance before era
256 $DbResult = $this->Database->query('SELECT * FROM Subject');
257 while($Subject = $DbResult->fetch_assoc())
258 {
259 $DbResult2 = $this->Database->query('SELECT SUM(money) FROM finance_operations WHERE user='.$Subject['Id'].' AND date < "2007-12-01"');
260 $Row = $DbResult2->fetch_row();
261 $TotalAdvance = $Row[0];
262 //echo($User['second_name'].' '.$User['first_name'].' '.$TotalAdvance.'<br />');
263 // Záloha
264 if($TotalAdvance > 0)
265 {
266 $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í)');
267 }
268 // Dluh
269 if($TotalAdvance < 0)
270 {
271 $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í)');
272 //echo($Database->LastQuery.'<br />');
273 }
274 }
275
276 // Transfer finance after era
277 // $DbResult = $Database->query('SELECT * FROM users WHERE role=2');
278 // while($User = $DbResult->fetch_array())
279 // {
280 $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"');
281 while($Operation = $DbResult2->fetch_assoc())
282 {
283 //echo($Operation['comment'].'<br />');
284 if(substr($Operation['comment'], 0, 19) == 'Poplatek za měsíc')
285 {
286 $this->InsertLiability($Operation['user'], -$Operation['money'], MysqlDateToTime($Operation['date']), $Operation['bill_id'], 'Připojení k síti');
287 $Output .= $Operation['user'].' '.$Operation['money'].' Připojení k síti<br />';
288 } else
289 if($Operation['comment'] == 'Vklad')
290 {
291 $this->InsertMoney($Operation['user'], $Operation['money'], 0, 1, MysqlDateToTime($Operation['date']), 'Přijatá záloha');
292 //echo($Operation['user'].' '.$Operation['money'].' Přijatá záloha<br />');
293 } else
294 if($Operation['comment'] == 'Internet')
295 {
296 $this->InsertLiability($Operation['user'], $Operation['money'], MysqlDateToTime($Operation['date']), $Operation['bill_id'], 'Měsíční paušál za Internet', MysqlDateToTime($Operation['date']));
297 $this->InsertMoney($Operation['user'], $Operation['money'], 0, 1, MysqlDateToTime($Operation['date']), 'Měsíční paušál za Internet');
298 } else
299 if($Operation['comment'] == 'Sociální pojištění')
300 {
301 $this->InsertLiability($Operation['user'], $Operation['money'], MysqlDateToTime($Operation['date']), $Operation['bill_id'], $Operation['comment'], MysqlDateToTime($Operation['date']));
302 $this->InsertMoney($Operation['user'], $Operation['money'], 0, 1, MysqlDateToTime($Operation['date']), $Operation['comment']);
303 } else
304 {
305 $DbResult = $this->Database->query('SELECT Id FROM Subject WHERE Id='.$Operation['user']);
306 while($User = $DbResult->fetch_array())
307 $this->InsertLiability($Operation['user'], -$Operation['money'], MysqlDateToTime($Operation['date']), $Operation['bill_id'], $Operation['comment'], MysqlDateToTime($Operation['date']));
308 $Output .= $Operation['user'].' '.$Operation['comment'].' '.MysqlDateToTime($Operation['date']).'<br />';
309
310// if(($Operation['role'] == 2))
311 // {
312 $Output .= 'A';
313 $this->Database->insert('FinanceAdvances', array('Subject' => $Operation['user'], 'Value' => $Operation['money'], 'TimeCreation' => MysqlDateToTime($Operation['date']), 'CashFlowId' => 0, 'Direction' => 'In'));
314 $this->CheckAdvancesAndLiabilities($Operation['user']);
315 //} else InsertMoney($Operation['user'], $Operation['money'], 0, 1, MysqlDateToTime($Operation['date']), $Operation['comment']);
316 }
317 }
318 // }
319
320 // Import small asset
321 $Subject = 1;
322
323 // Převod pro minulý rok
324 $Time = mktime(0, 0, 0, 12, 10, 2007);
325 $DbResult = $this->Database->query('SELECT * FROM network_devices WHERE used <> 0 AND date < "'.TimeToMysqlDateTime($Time).'" AND (id <> 73)');
326 $Items = array();
327 $TotalPrice = 0;
328 while($Device = $DbResult->fetch_array())
329 {
330 //$Database->insert('FinanceSmallAsset', array());
331 $Items[] = array('Description' => $Device['name'], 'Quantity' => $Device['count'], 'Price' => 0); //$Device['price']);
332 $LastId = $Device['id'];
333 $TotalPrice += $Device['price'] * $Device['count'];
334 if($TotalPrice > 55000) break;
335 }
336 //print_r($Items);
337 $BillId = $this->System->Modules['Bill']->CreateBill($Subject, $Items, $Time, $Time);
338 $DbResult = $this->Database->query('UPDATE network_devices SET TimeEnlistment = "'.TimeToMysqlDateTime($Time).'" WHERE used <> 0 AND date < "'.TimeToMysqlDateTime($Time).'" AND (id <> 73) AND (id <= '.$LastId.')');
339 //echo($Database->error);
340 $this->InsertLiability($Subject, 0, $Time, $BillId, 'Nákup infrastruktury', $Time);
341 //InsertMoney($Subject, 0, 0, 1, $Time, 'Nákup infrastruktury');
342
343 //echo($LastId);
344
345 // Převod tento rok
346 $Time = mktime(0, 0, 0, 1, 14, 2008);
347 $DbResult = $this->Database->query('SELECT * FROM network_devices WHERE (used <> 0) AND (id > '.$LastId.') AND date < "'.TimeToMysqlDateTime($Time).'"');
348 $Items = array();
349 $TotalPrice = 0;
350 while($Device = $DbResult->fetch_array())
351 {
352 // $Database->insert('FinanceSmallAsset', array());
353 $Items[] = array('Description' => $Device['name'], 'Quantity' => $Device['count'], 'Price' => 0); //$Device['price']);
354 $TotalPrice += $Device['price'] * $Device['count'];
355 }
356 //print_r($Items);
357 $BillId = $this->System->Modules['Bill']->CreateBill($Subject, $Items, $Time, $Time);
358 $DbResult = $this->Database->query('UPDATE network_devices SET TimeEnlistment = "'.TimeToMysqlDateTime($Time).'" WHERE used <> 0 AND date < "'.TimeToMysqlDateTime($Time).'" AND (id > '.$LastId.')');
359 $this->InsertLiability($Subject, 0, $Time, $BillId, 'Nákup infrastruktury', $Time);
360 //InsertMoney($Subject, 0, 0, 1, $Time, 'Nákup infrastruktury');
361
362
363 // Make absolute value in monthly overall
364 //$Database->query('UPDATE finance_monthly_overall SET total_paid = ABS(total_paid)');
365 //$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)');
366 return($Output);
367 }
368 */
369
370 function ConvertPDFDataToFiles()
371 {
372 $DbResult = $this->Database->query('SELECT * FROM FinanceBills');
373 while($Bill = $DbResult->fetch_assoc())
374 {
375 file_put_contents('doklady/doklad_'.$Bill['id'].'.pdf', $Bill['pdf']);
376 echo($Bill['id'].',');
377 }
378 }
379
380 function CheckPDFFiles()
381 {
382 global $InvoiceGenerator;
383
384 $DbResult = $this->Database->query('SELECT * FROM FinanceBills');
385 while($Bill = $DbResult->fetch_assoc())
386 {
387 file_put_contents('doklady2/doklad-'.$Bill['id'].'.pdf', file_get_contents('doklady/doklad-'.$Bill['id'].'.pdf'));
388 //echo($InvoiceGenerator->HasPDFFile($Bill['id']).',');
389 }
390 }
391
392 /*
393 function ConvertData()
394 {
395 $Finance = $this->System->Modules['Finance'];
396 $Output = '';
397 $this->Database->query('TRUNCATE TABLE FinanceOperation');
398 $this->Database->query('TRUNCATE TABLE FinanceClaimsLiabilities');
399
400 // Move time of device price transformation
401 $this->Database->query('UPDATE finance_operations SET date="2007-11-30" WHERE comment = "Jednorázový poplatek za společné zařízení"');
402
403 // Transform old operations
404 $DbResult = $this->Database->query('SELECT * FROM finance_operations WHERE finance_operations.date >= "2007-12-01" AND (Source = '.$Finance->ExternalSubject.')');
405 while($DbRow = $DbResult->fetch_assoc())
406 {
407 $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']));
408 $Output .= '.';
409 }
410 $DbResult = $this->Database->query('SELECT * FROM finance_operations WHERE finance_operations.date >= "2007-12-01" AND (Destination = '.$Finance->ExternalSubject.')');
411 while($DbRow = $DbResult->fetch_assoc())
412 {
413 $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']));
414 $Output .= '.';
415 }
416
417 // Transform old invoices
418 $DbResult = $this->Database->query('SELECT * FROM finance_operations WHERE finance_operations.date >= "2007-12-01" AND (Source = '.$Finance->MainSubject.')');
419 while($DbRow = $DbResult->fetch_assoc())
420 {
421 $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']));
422 $Output .= 'FV '.$DbRow['comment'].'<br />';
423 }
424 $DbResult = $this->Database->query('SELECT * FROM finance_operations WHERE finance_operations.date >= "2007-12-01" AND (Destination = '.$Finance->MainSubject.')');
425 while($DbRow = $DbResult->fetch_assoc())
426 {
427 $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']));
428 $Output .= 'FP '.$DbRow['comment'].'<br />';
429 }
430
431 // Transfer finance before era
432 $DbResult = $this->Database->query('SELECT * FROM Subject');
433 while($Subject = $DbResult->fetch_assoc())
434 {
435 $DbResult2 = $this->Database->query('SELECT SUM(money) as money FROM finance_operations WHERE user='.$Subject['Id'].' AND date < "2007-12-01"');
436 $DbRow2 = $DbResult2->fetch_assoc();
437
438 $DbRow2['date'] = TimeToMysqlDateTime(mktime(0, 0, 0, 12, 1, 2007));
439 if($DbRow2['money'] > 0)
440 {
441 $Comment = 'Přijatá záloha (z období před daňovou evidencí)';
442 $this->Database->insert('FinanceOperation', array('Subject' => $Subject['Id'], 'Cash' => 0, 'Value' => abs($DbRow2['money']), 'Time' => $DbRow2['date'], 'Taxable' => 1, 'Text' => $Comment));
443} else
444 {
445 $Comment = 'Připojení k síti (z období před daňovou evidencí)';
446 $this->Database->insert('FinanceClaimsLiabilities', array('Subject' => $Subject['Id'], 'Value' => abs($DbRow2['money']), 'TimeCreation' => $DbRow2['date'], 'Text' => $Comment));
447 }
448 $Output .= '#';
449 }
450
451 return($Output);
452 }
453 */
454
455
456 function GetBillingPeriod($Period)
457 {
458 $MonthCount = $this->System->Modules['Finance']->BillingPeriods[$Period]['MonthCount'];
459 $PeriodFrom = mktime(0, 0, 0, date('n'), 1, date('Y'));
460 $PeriodTo = mktime(0, 0, 0, date('n') + $MonthCount - 1, date('t', mktime(0, 0, 0, date('n') + $MonthCount - 1, 1, date('Y'))) , date('Y'));
461 return(array('From' => $PeriodFrom, 'To' => $PeriodTo, 'MonthCount' => $MonthCount));
462 }
463
464 function ShowMonthlyPayment()
465 {
466 if(!$this->System->Modules['User']->CheckPermission('Finance', 'Manage')) return('Nemáte oprávnění');
467 $Output = '';
468
469 $Finance = &$this->System->Modules['Finance'];
470
471 // Načti poslední měsíční přehled a nastavení
472 $DbResult = $this->Database->select('finance_monthly_overall', '*', '1 ORDER BY date DESC LIMIT 1');
473 $Overall = $DbResult->fetch_array();
474
475 $Output -= 'Datum: '.date('j.n.Y').'<br />';
476
477 $Datum = explode('-', $Overall['date']);
478 $Mesic = date('m') + 0;
479
480 $Output .= $Finance->RecalculateSegmentParameters();
481 $Output .= $Finance->LoadTariffs();
482 //$InvoiceGenerator->CustomGenerate();
483 $Output .= $Finance->RecalculateMemberPayment();
484
485 // Celkovy prehled
486 $DbResult = $this->Database->query('SELECT SUM(price) FROM network_devices WHERE used = 1');
487 $Row = $DbResult->fetch_row();
488 $TotalDeviceCost = $Row[0];
489 $Output .= 'Celková cena zařízení: Zařízení('.$TotalDeviceCost;
490
491 $DbResult = $this->Database->query('SELECT SUM(price) FROM network_segments');
492 $Row = $DbResult->fetch_row();
493 $TotalSegmentDeviceCost = $Row[0];
494 $Output .= '), Segmenty('.$TotalSegmentDeviceCost;
495
496 $DbResult = $this->Database->query('SELECT SUM(NetworkDevice) FROM MemberPayment');
497 $Row = $DbResult->fetch_row();
498 $TotalMemberDeviceCost = $Row[0];
499 $Output .= '), Členové('.$TotalMemberDeviceCost.')<br />';
500
501 $DbResult = $this->Database->query('SELECT SUM(Cash) FROM MemberPayment');
502 $Row = $DbResult->fetch_row();
503 $TotalMemberCash = $Row[0];
504 $Output .= 'Stav pokladny: Členové('.$TotalMemberCash.')';
505
506 $DbResult = $this->Database->query('SELECT SUM(consumption) FROM network_devices WHERE used=1');
507 $Row = $DbResult->fetch_row();
508 $TotalConsumption = $Row[0];
509 $TotalConsumptionCost = $Finance->W2Kc($TotalConsumption);
510
511 $SpravaCelkem = $Finance->Sprava * $Finance->SpravaUsers;
512 $Output .= 'Kontrola placení (Zaplaceno-Sprava-Internet): '.$Finance->TotalPaid.'-'.$SpravaCelkem.'-'.$Finance->Internet.'='.($Finance->TotalPaid - $SpravaCelkem - $Finance->Internet).'<br />';
513
514 // Zkontrolovat odečtení měsíčního poplatku
515 //$Mesic = '1';
516 $Output .= 'Kontrola odečtení poplatků: Poslední měsíc-'.round($Datum[1]).' Aktuální měsíc-'.$Mesic.'<br />';
517 if($Mesic != $Datum[1])
518 {
519 $Output .= 'Odečítám měsíční poplatek...<br />';
520
521 // Generuj účetní položky
522 $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');
523 while($Member = $DbResult->fetch_assoc())
524 {
525 $Output .= $Member['SubjectName'].': ';
526 //$Member['UNIX_TIMESTAMP(Member.BillingPeriodLastDate)'] = MysqlDateTimeToTime('2009-04-01 1:1:1');
527 $Period = $this->GetBillingPeriod($Member['BillingPeriodNext']);
528 if($Period['From'] > $Member['UNIX_TIMESTAMP(Member.BillingPeriodLastDate)'])
529 {
530 $this->Database->update('Member', 'Id='.$Member['Id'], array('BillingPeriod' => $Member['BillingPeriodNext'], 'InternetTariffCurrentMonth' => $Member['InternetTariffNextMonth']));
531 $Member['BillingPeriod'] = $Member['BillingPeriodNext'];
532 $Member['InternetTariffCurrentMonth'] = $Member['InternetTariffNextMonth'];
533 }
534 $Period = $this->GetBillingPeriod($Member['BillingPeriod']);
535 $PayPerPeriod = $Member['MonthlyTotal'] * $Period['MonthCount'];
536 if(($Period['From'] > $Member['UNIX_TIMESTAMP(Member.BillingPeriodLastDate)']) and ($Member['InternetTariffCurrentMonth'] != 6) and ($PayPerPeriod > 0) and
537 ($Member['Blocked'] == 0))
538 {
539 //echo($Mesic.'%'.$MonthCount.'='.($Mesic % $MonthCount).' ');
540 $TimePeriodText = date('j.n.Y', $Period['From']).' - '.date('j.n.Y', $Period['To']);
541 $Output .= $TimePeriodText.': '.$Member['MonthlyTotal'].' * '.$Period['MonthCount'].' = '.$PayPerPeriod.'<br />';
542 $BillCode = $Finance->GetNextDocumentLineNumber(6); // Faktury vydané
543 $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);
544 $this->Database->insert('FinanceClaimsLiabilities', array('Value' => $PayPerPeriod, 'Subject' => $Member['Subject'], 'TimeCreation' => 'NOW()', 'TimeDue' => 'DATE_ADD(NOW(), INTERVAL 15 DAY)', 'Text' => 'Připojení k síti za období '.$TimePeriodText, 'Bill' => 0, 'BillCode' => $BillCode));
545 $Output .= $this->SendPaymentEmail($Member['Id']);
546 $this->Database->update('Member', 'Id='.$Member['Id'], array('BillingPeriodLastDate' => TimeToMysqlDateTime($Period['To'])));
547 } else $Output .= '<br />';
548 }
549
550 // Update finance charge
551 $Output .= 'Měním aktuální parametry sítě...<br>';
552 $this->Database->delete('finance_charge', 'period = 0');
553 $DbResult = $this->Database->select('finance_charge', '*', 'period = 1');
554 $Charge = $DbResult->fetch_assoc();
555 //print_r($Charge);
556 $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']));
557
558 $Output .= 'Přidávám měsíční přehled...<br />';
559 $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));
560
561 $Output .= 'Měním aktuální tarify....<br>';
562 // Update tarrifs
563 $this->Database->delete('FinanceTariff', 'Period=0');
564 $DbResult = $this->Database->select('FinanceTariff', '*', 'Period = 1');
565 while($Tariff = $DbResult->fetch_array())
566 {
567 $this->Database->insert('FinanceTariff', array('Period' => 0, 'Name' => $Tariff['Name'], 'Tariff' => $Tariff['Tariff'], 'UploadAsymmetry' => $Tariff['UploadAsymmetry'], 'MemberCount' => $Tariff['MemberCount'], 'Group' => $Tariff['Group'], 'SpeedMin' => $Tariff['SpeedMin'], 'SpeedMax' => $Tariff['SpeedMax'], 'Price' => $Tariff['Price']));
568 }
569
570 $Finance->RecalculateMemberPayment();
571 //CreateMonthlyOverallBill();
572 //$Finance->RecalculateUsersFinance();
573
574 // Restart traffic shaping
575 //$this->Database->update('NetworkConfiguration', 'Id = 3', array('Changed' => 1));
576 }
577 return($Output);
578 }
579
580 function SendPaymentEmail($MemberId)
581 {
582 global $Config;
583
584 $DbResult = $this->Database->select('Member', '*', 'Id='.$MemberId);
585 $Member = $DbResult->fetch_assoc();
586
587 $DbResult = $this->Database->select('MemberPayment', '*', 'Member='.$MemberId);
588 $MemberPayment = $DbResult->fetch_assoc();
589
590 $DbResult = $this->Database->select('Subject', 'Name', 'Id='.$Member['Subject']);
591 $Subject = $DbResult->fetch_assoc();
592
593 $DbResult = $this->Database->select('User', '*', 'Id='.$Member['ResponsibleUser']);
594 $User = $DbResult->fetch_assoc();
595
596 if($User['Email'] != '')
597 {
598 $Title = 'Pravidelné vyúčtování internetu';
599 $Content = 'Vyúčtovaní subjektu <strong>'.$Subject['Name'].'</strong> zastoupeného uživatelem <strong>'.$User['Name'].'</strong> ke dni <strong>'.$this->System->HumanDate(time()).'</strong>.<br /><br />'."\n".
600 'Váš aktuální tarif: <strong>'.$this->System->Modules['Finance']->Tariffs[$Member['InternetTariffCurrentMonth']]['Name'].' '.$this->System->AddPrefixMultipliers($this->System->Modules['Finance']->Tariffs[$Member['InternetTariffCurrentMonth']]['SpeedMax'], 'bit/s', 3, 'Binary').'</strong><br />'."\n".
601 'Vaše platební období: <strong>'.$this->System->Modules['Finance']->BillingPeriods[$Member['BillingPeriod']]['Name'].'</strong><br />'."\n".
602 'Pravidelná platba za období: <strong>'.$MemberPayment['MonthlyTotal'].' Kč</strong><br />'."\n".
603 'Stav vašeho účtu: <strong>'.($MemberPayment['Cash'] - $MemberPayment['MonthlyTotal']).' Kč</strong><br /><br />'."\n";
604 $Content .= 'Nové finanční operace:<br/>'.
605 '<table style="margin-left: auto; margin-right: auto; border-style: solid; border-width: 1px; border-collapse: collapse;"><tr><th style="border-style: solid; border-width: 1px; padding: 1px 5px 1px 5px; text-align: center; font-weight: bold;">Čas</th><th style="border-style: solid; border-width: 1px; padding: 1px 5px 1px 5px; text-align: center;; font-weight: bold;">Popis</th><th style="border-style: solid; border-width: 1px; padding: 1px 5px 1px 5px; text-align: center;; font-weight: bold;">Částka [Kč]</th></tr>'."\n";
606 //$Member['BillingPeriodLastDate'] = '2009-04-01';
607 $DbResult = $this->Database->query('SELECT T1.* FROM ((SELECT Text, Time, Value AS Value, Bill FROM FinanceOperation WHERE (Subject='.$Member['Subject'].')) UNION ALL '.
608 '(SELECT Text, TimeCreation as Time, -Value as Value, Bill FROM FinanceClaimsLiabilities WHERE (Subject='.$Member['Subject'].')) ORDER BY Time DESC) AS T1 WHERE (T1.Time > "'.$Member['BillingPeriodLastDate'].'")');
609 while($DbRow = $DbResult->fetch_assoc())
610 {
611 $Content .= '<tr><td style="border-style: solid; border-width: 1px; padding: 1px 5px 1px 5px; text-align: center;">'.HumanDate($DbRow['Time']).'</td><td style="border-style: solid; border-width: 1px; padding: 1px 5px 1px 5px; text-align: center;">'.$DbRow['Text'].'</td><td style="border-style: solid; border-width: 1px; padding: 1px 5px 1px 5px; text-align: center;">'.$DbRow['Value'].'</td></tr>'."\n";
612 }
613 $Content .= '</table><br />'."\n".
614 'Pro aktuální informace, prohlížení elektronických dokladů a možnost změny údajů se prosím přihlašte na stránkách <a href="http://'.$Config['Web']['Host'].$Config['Web']['RootFolder'].'">http://'.$Config['Web']['Host'].$Config['Web']['RootFolder'].'</a>.<br /><br />'."\n";
615
616 $Content .= '<br />Tento email je generován automaticky. V případě zjištění nesrovnalostí napište zpět.';
617 $AdditionalHeaders = 'To: =?UTF-8?B?'.base64_encode($User['Name']).'?= <'.$User['Email'].">\n".'From: =?UTF-8?B?'.base64_encode($Config['Web']['Title']).'?='." <".$Config['Web']['AdminEmail'].">\n";
618 $this->System->AddEmailToQueue($User['Email'], $Title, $Content, $AdditionalHeaders);
619 $Output = '';
620 } else $Output = 'Uživatel '.$User['Name'].' nemá email.';
621 }
622
623 function GenerateBills()
624 {
625 // FinanceClaimsLiablities
626 $DbResult = $this->Database->query('SELECT * FROM FinanceClaimsLiabilities WHERE (BillCode <> "") AND (Value >= 0)');
627 while($Row = $DbResult->fetch_assoc())
628 {
629 $BillId = $this->System->Modules['Bill']->CreateBill($Row['Subject'], array(array('Description' => $Row['Text'], 'Price' => $Row['Value'], 'Quantity' => 1)), MysqlDateTimeToTime($Row['TimeCreation']), MysqlDateTimeToTime($Row['TimeDue']), $Row['BillCode']);
630 $this->Database->update('FinanceClaimsLiabilities', 'Id='.$Row['Id'], array('Bill' => $BillId));
631 echo('.');
632 }
633
634 // FinanceOperations
635 }
636}
637
638$System->AddModule(new FinanceManagePage());
639$System->Modules['FinanceManagePage']->GetOutput();
640
641?>
Note: See TracBrowser for help on using the repository browser.