source: trunk/finance/manage.php@ 302

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