source: trunk/finance/manage.php@ 510

Last change on this file since 510 was 510, checked in by chronos, 12 years ago
  • Opraveno: Generovat platby pouze pokud je změna měsíce.
  • Přidáno: Dočasné navrácení importovacího skriptu z výpisu plateb poštovní spořitelny.
File size: 28.2 KB
Line 
1<?php
2
3include_once(dirname(__FILE__).'/../Common/Global.php');
4
5class FinanceManagePage extends Page
6{
7 var $FullTitle = 'Správa financí';
8 var $ShortTitle = 'Správa financí';
9
10 function Show()
11 {
12 if(!$this->System->Modules['User']->CheckPermission('Finance', 'Manage'))
13 return('Nemáte oprávnění');
14
15 if(array_key_exists('Operation', $_GET)) $Operation = $_GET['Operation'];
16 else $Operation = '';
17 //$Operation = 'ImportOldData';
18 switch($Operation)
19 {
20 case 'Bills':
21 $Output = $this->BillManage();
22 break;
23 case 'ConvertPDFToFile':
24 $Output = $this->ConvertPDFDataToFiles();
25 break;
26 //case 'ImportOldData':
27 // $Output = $this->ImportOldData();
28 // break;
29 case 'NewDeviceForm':
30 $Output = $this->ShowNewDeviceForm();
31 break;
32 case 'NewDeviceInsert':
33 $Output = $this->ShowNewDeviceInsert();
34 break;
35 case 'NewDeviceHistoryForm':
36 $Output = $this->ShowNewDeviceHistoryForm();
37 break;
38 case 'NewDeviceHistoryInsert':
39 $Output = $this->ShowNewDeviceHistoryInsert();
40 break;
41 case 'NewPaymentInsert':
42 $Output = $this->ShowNewPaymentInsert();
43 break;
44 case 'NewPaymentForm':
45 $Output = $this->ShowNewPaymentForm();
46 break;
47 case 'NewInvoiceInsert':
48 $Output = $this->ShowNewInvoiceInsert();
49 break;
50 case 'NewInvoiceForm':
51 $Output = $this->ShowNewInvoiceForm();
52 break;
53 case 'Recalculate':
54 $Output = $this->System->Modules['Finance']->RecalculateSegmentParameters();
55 $Output .= $this->System->Modules['Finance']->RecalculateMemberPayment();
56 break;
57 case 'ShowMonthlyPayment':
58 $Output = $this->ShowMonthlyPayment();
59 break;
60 case 'ProcessMonthlyPayment':
61 $Output = $this->ProcessMonthlyPayment();
62 break;
63 case 'GenerateBills':
64 $Output = $this->GenerateBills();
65 break;
66 default:
67 $Output = '<a href="?Operation=NewDeviceForm">Přidat nové zařízení</a><br />';
68 $Output .= '<a href="?Operation=NewDeviceHistoryForm">Přidat nový záznam historie zařízení</a><br />';
69 $Output .= '<a href="?Operation=NewPaymentForm">Přidat novou platbu</a><br />';
70 $Output .= '<a href="?Operation=NewInvoiceForm">Přidat novou fakturu</a><br />';
71 $Output .= '<a href="?Operation=ConvertPDFToFile">Převést data z databáze do souborů</a><br />';
72 $Output .= '<a href="?Operation=Bills">Správa dokladů</a><br />';
73 //$Output .= '<a href="?Operation=Recalculate">Přepočet financí</a><br />';
74 $Output .= '<a href="?Operation=ShowMonthlyPayment">Měsíční vyúčtování</a><br />';
75 //$Output .= '<a href="clenove.php">Seznam členů</a><br />';
76 $Output .= '<a href="zivnost.php">Živnost</a><br />';
77 $Output .= '<a href="?Operation=GenerateBills">Generovat chybějící doklady</a><br />';
78 $Output .= '<a href="import.php">Import plateb</a><br />';
79 }
80 return($Output);
81 }
82
83 function BillManage()
84 {
85 $Output = '';
86 if(array_key_exists('subject', $_GET))
87 {
88 $DbResult = $this->Database->select('FinanceBills', '*', 'Subject='.$_GET['subject']);
89 while($Item = $DbResult->fetch_array())
90 {
91 $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 />';
92 }
93 } else
94 if(array_key_exists('billpdf', $_GET))
95 {
96 $Output .= $this->System->Modules['Bill']->ShowStoredBill($_GET['billpdf']);
97 } else
98 if(array_key_exists('billpdf2', $_GET))
99 {
100 $Output .= $this->System->Modules['Bill']->ShowGeneratedBill($_GET['billpdf2']);
101 } else
102 if(array_key_exists('regenerate', $_GET))
103 {
104 $Output .= $this->System->Modules['Bill']->RegeneratePDF($_GET['regenerate']);
105 } else
106 if(array_key_exists('bill', $_GET))
107 {
108 $Output .= $this->System->Modules['Bill']->GenerateBill($_GET['bill']);
109 } else
110 if(array_key_exists('generate', $_GET))
111 {
112 $Output .= $this->System->Modules['Bill']->CreateBill(1, array(array('Description' => 'Poplatek za připojení k Internetu', 'Price' => 1000, 'Quantity' => 1)), time(), time());
113 } else
114 {
115 //ShowHeader('Faktury', 'Faktury');
116 $Output .= 'Faktury:<br />';
117 $DbResult = $this->Database->select('Subject', '*', '1 ORDER BY Name');
118 while($Subject = $DbResult->fetch_array())
119 {
120 $Output .= '<a href="?Operation=Bills&amp;subject='.$Subject['Id'].'">'.$Subject['Name'].'</a><br />';
121 }
122 //ShowFooter();
123 }
124 return($Output);
125 }
126
127 function CheckAdvancesAndLiabilities($Subject)
128 {
129 global $LastInsertTime;
130
131 do {
132 $DbResult = $this->Database->select('FinanceAdvances', 'SUM(Value)', 'Subject='.$Subject.' AND Direction = "In"');
133 $DbRow = $DbResult->fetch_array();
134 $Advances = $DbRow[0];
135 $DbResult = $this->Database->select('FinanceInvoice', '*', 'Subject='.$Subject.
136 ' AND TimePayment IS NULL AND Value > 0 ORDER BY TimeCreation LIMIT 1');
137 if($DbResult->num_rows > 0)
138 {
139 $OpenedClaim = $DbResult->fetch_array();
140 if($Advances > $OpenedClaim['Value'])
141 {
142 $this->Database->update('FinanceInvoice', 'Id='.$OpenedClaim['Id'],
143 array('TimePayment' => TimeToMysqlDateTime($LastInsertTime)));
144 $this->Database->insert('FinanceAdvances', array('Subject' =>
145 $OpenedClaim['Subject'], 'Value' => -$OpenedClaim['Value'],
146 'TimeCreation' => TimeToMysqlDateTime($LastInsertTime),
147 'CashFlowId' => $OpenedClaim['Id'], 'Direction' => 'In'));
148 } else break;
149 } else break;
150 } while(($Advances > $OpenedClaim['Value']));
151 }
152
153 function InsertInvoice($Subject, $TimeCreation, $TimeDue, $Items,
154 $DocumentLine, $PeriodFrom, $PeriodTo)
155 {
156 global $LastInsertTime;
157
158 $BillCode = $this->System->Modules['Finance']->GetNextDocumentLineNumber($DocumentLine);
159 $SumValue = 0;
160 foreach($Items as $Item)
161 $SumValue = $SumValue + $Item['Price'];
162 $this->Database->insert('FinanceInvoice', array(
163 'Subject' => $Subject, 'TimeCreation' => TimeToMysqlDateTime($TimeCreation),
164 'TimeDue' => TimeToMysqlDateTime($TimeDue), 'Value' => $SumValue, 'BillCode' => $BillCode,
165 'PeriodFrom' => TimeToMysqlDate($PeriodFrom), 'PeriodTo' => TimeToMysqlDate($PeriodTo)));
166 $InvoiceId = $this->Database->insert_id;
167 foreach($Items as $Item)
168 $this->Database->insert('FinanceInvoiceItem', array('FinanceInvoice' => $InvoiceId,
169 'Description' => $Item['Description'], 'Price' => $Item['Price'], 'Quantity' => $Item['Quantity'], 'VAT' => $Item['VAT']));
170 //$LastInsertTime = $Time;
171 //$this->CheckAdvancesAndLiabilities($Subject);
172 return($InvoiceId);
173 }
174
175 function InsertMoney($Subject, $Value, $Cash, $Taxable, $Time, $Text, $DocumentLine)
176 {
177 global $LastInsertTime;
178
179 $BillCode = $this->System->Modules['Finance']->GetNextDocumentLineNumber($DocumentLine);
180 $this->Database->insert('FinanceOperation', array('Text' => $Text, 'Subject' => $Subject, 'Cash' => $Cash, 'Value' => $Value, 'Time' => TimeToMysqlDateTime($Time), 'Taxable' => $Taxable, 'BillCode' => $BillCode));
181 /*if($Value >= 0)
182 {
183 $this->Database->insert('FinanceAdvances', array('Subject' => $Subject, 'Value' => $Value, 'TimeCreation' => TimeToMysqlDateTime($Time), 'CashFlowId' => $this->Database->insert_id, 'Direction' => 'In'));
184 }
185 $LastInsertTime = $Time;
186 */
187 //$this->CheckAdvancesAndLiabilities($Subject);
188 }
189
190 function ShowNewDeviceForm()
191 {
192 $Form = new Form($this->System->FormManager);
193 $Form->SetClass('NewNetworkDevice');
194 $Form->OnSubmit = '?Operation=NewDeviceInsert';
195 $Output = $Form->ShowEditForm();
196 return($Output);
197 }
198
199 function ShowNewDeviceInsert()
200 {
201 $Form = new Form($this->System->FormManager);
202 $Form->SetClass('NewNetworkDevice');
203 $Form->LoadValuesFromForm();
204 $Form->SaveValuesToDatabase(0);
205 $Output = $this->SystemMessage('Finance', 'Zařízení vloženo.');
206 $this->System->Modules['Log']->NewRecord('Finance', 'NewDeviceInserted');
207 return($Output);
208 }
209
210 function ShowNewDeviceHistoryForm()
211 {
212 $Form = new Form($this->System->FormManager);
213 $Form->SetClass('NewNetworkDeviceHistory');
214 $Form->OnSubmit = '?Operation=NewDeviceHistoryInsert';
215 $Output = $Form->ShowEditForm();
216 return($Output);
217 }
218
219 function ShowNewDeviceHistoryInsert()
220 {
221 $Form = new Form($this->System->FormManager);
222 $Form->SetClass('NewNetworkDeviceHistory');
223 $Form->LoadValuesFromForm();
224 $Form->SaveValuesToDatabase(0);
225 $Output = $this->SystemMessage('Finance', 'Záznam historie zařízení vložen.');
226 $this->System->Modules['Log']->NewRecord('Finance', 'NewDeviceHistoryInserted');
227 return($Output);
228 }
229
230 function ShowNewPaymentForm()
231 {
232 $Form = new Form($this->System->FormManager);
233 $Form->SetClass('NewPayment');
234 $Form->OnSubmit = '?Operation=NewPaymentInsert';
235 $Output = $Form->ShowEditForm();
236 return($Output);
237 }
238
239 function ShowNewPaymentInsert()
240 {
241 $Form = new Form($this->System->FormManager);
242 $Form->SetClass('NewPayment');
243 $Form->LoadValuesFromForm();
244 $this->InsertMoney($Form->Values['Subject'], $Form->Values['Value'], $Form->Values['Cash'], $Form->Values['Taxable'], $Form->Values['Time'], $Form->Values['Text'], $Form->Values['DocumentLine']);
245 $LastId = $this->Database->insert_id;
246 $DbResult = $this->Database->select('FinanceOperation', 'BillCode', 'Id='.$LastId);
247 $DbRow = $DbResult->fetch_assoc();
248 $Output = $this->SystemMessage('Finance', 'Platba vložena '.$DbRow['BillCode'].'.');
249 $this->System->Modules['Log']->NewRecord('Finance', 'NewPaymentInserted');
250 return($Output);
251 }
252
253 function ShowNewInvoiceForm()
254 {
255 $Form = new Form($this->System->FormManager);
256 $Form->SetClass('NewInvoice');
257 $Form->OnSubmit = '?Operation=NewInvoiceInsert';
258 $Output = $Form->ShowEditForm();
259 return($Output);
260 }
261
262 function ShowNewInvoiceInsert()
263 {
264 $Form = new Form($this->System->FormManager);
265 $Form->SetClass('NewInvoice');
266 $Form->LoadvaluesFromForm();
267 $Items = array(array('Price' => $Form->Values['Value'],
268 'Description' => $Form->Values['Text'], 'Quantity' => 1, 'VAT' => 21));
269 $LastId = $this->InsertInvoice($Form->Values['Subject'],
270 $Form->Values['TimeCreation'], $Form->Values['TimeDue'], $Items,
271 $Form->Values['DocumentLine'], NULL, NULL);
272 $DbResult = $this->Database->select('FinanceInvoice', 'BillCode', 'Id='.$LastId);
273 $DbRow = $DbResult->fetch_assoc();
274 $Output = $this->SystemMessage('Finance', 'Faktura vložena '.$DbRow['BillCode'].'.');
275 $this->System->Modules['Log']->NewRecord('Finance', 'NewInvoiceInserted');
276 return($Output);
277 }
278
279 function ConvertPDFDataToFiles()
280 {
281 $DbResult = $this->Database->query('SELECT * FROM FinanceBills');
282 while($Bill = $DbResult->fetch_assoc())
283 {
284 file_put_contents('doklady/doklad_'.$Bill['id'].'.pdf', $Bill['pdf']);
285 echo($Bill['id'].',');
286 }
287 }
288
289 function CheckPDFFiles()
290 {
291 global $InvoiceGenerator;
292
293 $DbResult = $this->Database->query('SELECT * FROM FinanceBills');
294 while($Bill = $DbResult->fetch_assoc())
295 {
296 file_put_contents('doklady2/doklad-'.$Bill['id'].'.pdf', file_get_contents('doklady/doklad-'.$Bill['id'].'.pdf'));
297 //echo($InvoiceGenerator->HasPDFFile($Bill['id']).',');
298 }
299 }
300
301 function GetBillingPeriod($Period)
302 {
303 $MonthCount = $this->System->Modules['Finance']->BillingPeriods[$Period]['MonthCount'];
304 $PeriodFrom = mktime(0, 0, 0, date('n'), 1, date('Y'));
305 $PeriodTo = mktime(0, 0, 0, date('n') + $MonthCount - 1, date('t', mktime(0, 0, 0, date('n') + $MonthCount - 1, 1, date('Y'))) , date('Y'));
306 return(array('From' => $PeriodFrom, 'To' => $PeriodTo, 'MonthCount' => $MonthCount));
307 }
308
309 function ShowMonthlyPayment()
310 {
311 if(!$this->System->Modules['User']->CheckPermission('Finance', 'Manage')) return('Nemáte oprávnění');
312 $SQL = 'SELECT Member.*, MemberPayment.MonthlyTotal AS Monthly, '.
313 'MemberPayment.Cash AS Cash, '.
314 '(SELECT GROUP_CONCAT(Service.Name) FROM ServiceCustomerRel LEFT JOIN Service '.
315 'ON Service.Id=ServiceCustomerRel.Service WHERE ServiceCustomerRel.Customer=Member.Id AND '.
316 'ServiceCustomerRel.Action IS NULL) AS ServicesNextMonth, '.
317 'UNIX_TIMESTAMP(Member.BillingPeriodLastDate) AS LastDate, Subject.Name AS SubjectName, '.
318 'FinanceBillingPeriod.Name AS BillingPeriodName '.
319 'FROM MemberPayment JOIN Member ON Member.Id=MemberPayment.Member JOIN Subject '.
320 'ON Subject.Id=Member.Subject LEFT JOIN FinanceBillingPeriod ON '.
321 'FinanceBillingPeriod.Id=Member.BillingPeriodNext WHERE (Member.Blocked = 0)'.
322 'AND (Member.BillingPeriodNext > 1) AND (MemberPayment.MonthlyTotal != 0)';
323
324 $DbResult = $this->Database->query('SELECT COUNT(*) FROM ('.$SQL.') AS T');
325 $DbRow = $DbResult->fetch_row();
326 $PageList = GetPageList($DbRow[0]);
327
328 $Output = $PageList['Output'];
329 $Output .= '<table class="WideTable" style="font-size: small;">';
330
331 $TableColumns = array(
332 array('Name' => 'SubjectName', 'Title' => 'Jméno'),
333 array('Name' => 'Monthly', 'Title' => 'Platba'),
334 array('Name' => 'Cash', 'Title' => 'Kredit'),
335 array('Name' => 'LastDate', 'Title' => 'Poslední fakturace'),
336 array('Name' => 'ServicesNextMonth', 'Title' => 'Služby'),
337 array('Name' => 'BillingPeriodName', 'Title' => 'Perioda'),
338 );
339 $Order = GetOrderTableHeader($TableColumns, 'SubjectName', 0);
340 $Output .= $Order['Output'];
341
342 $Query = $SQL.' '.$Order['SQL'].$PageList['SQLLimit'];
343
344 $DbResult = $this->Database->query($Query);
345 while($Row = $DbResult->fetch_assoc())
346 {
347 $Output .= '<tr>'.
348 '<td>'.$Row['SubjectName'].'</td>'.
349 '<td>'.$Row['Monthly'].'</td>'.
350 '<td>'.$Row['Cash'].'</td>'.
351 '<td>'.date('j.n.Y', $Row['LastDate']).'</td>'.
352 '<td>'.$Row['ServicesNextMonth'].'</td>'.
353 '<td>'.$Row['BillingPeriodName'].'</td>'.
354 '</tr>';
355 }
356 $Output .= '</table>';
357 $Output .= $PageList['Output'];
358 $Output .= '<a href="?Operation=ProcessMonthlyPayment">Generovat faktury</a>';
359 return($Output);
360 }
361
362 function ProduceInvoices()
363 {
364 $Output = '';
365
366 // Generuj účetní položky
367 $DbResult = $this->Database->query('SELECT Member.*, MemberPayment.MonthlyTotal AS MonthlyTotal, '.
368 'UNIX_TIMESTAMP(Member.BillingPeriodLastDate), Subject.Name AS SubjectName,'.
369 'MemberPayment.MonthlyPlus AS MonthlyPlus '.
370 'FROM MemberPayment JOIN Member ON Member.Id=MemberPayment.Member '.
371 'JOIN Subject ON Subject.Id=Member.Subject');
372 while($Member = $DbResult->fetch_assoc())
373 {
374 $Output .= $Member['SubjectName'].': ';
375 $Period = $this->GetBillingPeriod($Member['BillingPeriodNext']);
376 if($Period['From'] > $Member['UNIX_TIMESTAMP(Member.BillingPeriodLastDate)'])
377 {
378 $this->Database->update('Member', 'Id='.$Member['Id'],
379 array('BillingPeriod' => $Member['BillingPeriodNext']));
380 $Member['BillingPeriod'] = $Member['BillingPeriodNext'];
381 }
382 $Period = $this->GetBillingPeriod($Member['BillingPeriod']);
383 if(($Period['MonthCount'] > 0) and ($Member['Blocked'] == 0) and
384 ($Period['From'] > $Member['UNIX_TIMESTAMP(Member.BillingPeriodLastDate)']))
385 {
386 $InvoiceItems = array();
387 $MonthlyTotal = 0;
388 $DbResult2 = $this->Database->query('SELECT Service.*, FinanceVAT.Value AS VAT '.
389 'FROM ServiceCustomerRel LEFT JOIN Service '.
390 'ON Service.Id=ServiceCustomerRel.Service '.
391 'LEFT JOIN FinanceVAT ON FinanceVAT.Id=Service.VAT '.
392 'WHERE (ServiceCustomerRel.Customer='.
393 $Member['Id'].') AND (ServiceCustomerRel.Action IS NULL) ');
394 while($Service = $DbResult2->fetch_assoc())
395 {
396 $InvoiceItems[] = array('Description' => $Service['Name'], 'Price' => $Service['Price'],
397 'Quantity' => $Period['MonthCount'], 'VAT' => $Service['VAT']);
398 $MonthlyTotal += $Service['Price'];
399 }
400 if($Member['MonthlyPlus'] != 0)
401 {
402 $InvoiceItems[] = array('Description' => 'Spotřeba energie', 'Price' => -$Member['MonthlyPlus'],
403 'Quantity' => $Period['MonthCount'], 'VAT' => 2);
404 $MonthlyTotal -= $Member['MonthlyPlus'];
405 }
406 $PayPerPeriod = $MonthlyTotal * $Period['MonthCount'];
407 if($PayPerPeriod != 0)
408 {
409 $TimePeriodText = date('j.n.Y', $Period['From']).' - '.date('j.n.Y', $Period['To']);
410 $Output .= $TimePeriodText.': '.$MonthlyTotal.' * '.$Period['MonthCount'].' = '.$PayPerPeriod.'<br />';
411 $this->InsertInvoice($Member['Subject'], time(), time() + 3600 * 24 * INVOICE_DUE_DAYS,
412 $InvoiceItems, INVOICE_OUT_DOC_LINE, $Period['From'], $Period['To']);
413
414 $Output .= $this->SendPaymentEmail($Member['Id']);
415 } else $Output .= '<br />';
416 $this->Database->update('Member', 'Id='.$Member['Id'],
417 array('BillingPeriodLastDate' => TimeToMysqlDateTime($Period['To'])));
418 } else $Output .= '<br />';
419 }
420 return($Output);
421 }
422
423 function TableUpdatePeriod($Table)
424 {
425 $DbResult = $this->Database->select($Table, '*', '`Action` IS NOT NULL');
426 while($Service = $DbResult->fetch_array())
427 {
428 if($Service['Action'] == 'add')
429 {
430 unset($Service['Id']);
431 unset($Serivce['ReplaceId']);
432 unset($Serivce['Action']);
433 $this->Database->insert($Table, $Service);
434 } else
435 if($Service['Action'] == 'modify')
436 {
437 unset($Service['Id']);
438 unset($Service['Action']);
439 $ReplaceId = $Service['ReplaceId'];
440 unset($Service['ReplaceId']);
441 $this->Database->update($Table, 'Id='.$ReplaceId, $Service);
442 } else
443 if($Service['Action'] == 'delete')
444 {
445 $this->Database->delete($Table, '`Id`='.$Service['ReplaceId']);
446 }
447 }
448 $this->Database->delete($Table, '`Action` IS NOT NULL');
449 }
450
451 function ProcessMonthlyPayment()
452 {
453 if(!$this->System->Modules['User']->CheckPermission('Finance', 'Manage')) return('Nemáte oprávnění');
454 $Output = '';
455
456 $Finance = &$this->System->Modules['Finance'];
457
458 // Načti poslední měsíční přehled a nastavení
459 $DbResult = $this->Database->select('FinanceMonthlyOverall', '*', '1 ORDER BY Date DESC LIMIT 1');
460 $Overall = $DbResult->fetch_array();
461
462 $Output -= 'Datum: '.date('j.n.Y').'<br />';
463
464 $DateParts = explode('-', $Overall['Date']);
465 $MonthLast = $DateParts[1];
466 $MonthCurrent = date('m') + 0;
467
468 $Output .= $Finance->RecalculateSegmentParameters();
469 $Output .= $Finance->LoadTariffs();
470 $Output .= $Finance->RecalculateMemberPayment();
471
472 // Celkovy prehled
473 $DbResult = $this->Database->query('SELECT SUM(Product.SellPrice) AS Price FROM StockItem '.
474 'JOIN Product ON StockItem.Product = Product.Id WHERE (StockItem.TimeElimination IS NULL) AND (StockItem.Segment IS NOT NULL)');
475 $Row = $DbResult->fetch_row();
476 $TotalDeviceCost = $Row[0];
477 $Output .= 'Celková cena zařízení: Zařízení('.$TotalDeviceCost;
478
479 $DbResult = $this->Database->query('SELECT SUM(Price) FROM NetworkSegment');
480 $Row = $DbResult->fetch_row();
481 $TotalSegmentDeviceCost = $Row[0];
482 $Output .= '), Segmenty('.$TotalSegmentDeviceCost;
483
484 $DbResult = $this->Database->query('SELECT SUM(NetworkDevice) FROM MemberPayment');
485 $Row = $DbResult->fetch_row();
486 $TotalMemberDeviceCost = $Row[0];
487 $Output .= '), Členové('.$TotalMemberDeviceCost.')<br />';
488
489 $DbResult = $this->Database->query('SELECT SUM(Cash) FROM MemberPayment');
490 $Row = $DbResult->fetch_row();
491 $TotalMemberCash = $Row[0];
492 $Output .= 'Stav pokladny: Členové('.$TotalMemberCash.')';
493
494 $DbResult = $this->Database->query('SELECT SUM(Product.Consumption) AS Consumption FROM StockItem '.
495 'JOIN Product ON StockItem.Product = Product.Id WHERE (StockItem.TimeElimination IS NULL) AND (StockItem.Segment IS NOT NULL)');
496 $Row = $DbResult->fetch_row();
497 $TotalConsumption = $Row[0];
498 $TotalConsumptionCost = $Finance->W2Kc($TotalConsumption);
499
500 $SpravaCelkem = $Finance->Sprava * $Finance->SpravaUsers;
501 $Output .= 'Kontrola placení (Zaplaceno-Sprava-Internet): '.$Finance->TotalPaid.'-'.$SpravaCelkem.'-'.$Finance->Internet.'='.($Finance->TotalPaid - $SpravaCelkem - $Finance->Internet).'<br />';
502
503 // Zkontrolovat odečtení měsíčního poplatku
504 $Output .= 'Kontrola odečtení poplatků: Poslední měsíc-'.$MonthLast.' Aktuální měsíc-'.$MonthCurrent.'<br />';
505 if($MonthCurrent != $MonthLast)
506 {
507 $Output .= 'Odečítám měsíční poplatek...<br />';
508 $Output .= $this->ProduceInvoices();
509
510 // Update finance charge
511 $Output .= 'Měním aktuální parametry sítě...<br>';
512 $this->TableUpdatePeriod('FinanceCharge');
513
514 $Output .= 'Přidávám měsíční přehled...<br />';
515 $DbResult = $this->Database->query('SELECT * FROM FinanceCharge WHERE Action IS NULL LIMIT 1');
516 $Charge = $DbResult->fetch_assoc();
517 $this->Database->insert('FinanceMonthlyOverall', array('Date' => 'NOW()',
518 'Money' => $Finance->Internet, 'kWh' => $Finance->kWh,
519 'Administration' => $Finance->Sprava, 'AdministrationTotal' => $SpravaCelkem,
520 'ConsumptionTotal' => $TotalConsumptionCost, 'TotalPaid' => $Finance->TotalPaid,
521 'BaseTariffPrice' => $Charge['BaseTariffPrice'],
522 'TopTariffPrice' => $Charge['TopTariffPrice'], 'MemberCount' => $Finance->InternetUsers));
523
524 // Update services
525 $Output .= 'Aktualizuji služby....<br>';
526 $this->TableUpdatePeriod('Service');
527 // Update customer service selections
528 $Output .= 'Aktualizuji výběr služeb zákazníků....<br>';
529 $this->TableUpdatePeriod('ServiceCustomerRel');
530
531 $Finance->RecalculateMemberPayment();
532 //CreateMonthlyOverallBill();
533 //$Finance->RecalculateUsersFinance();
534
535 // Restart traffic shaping
536 //$this->Database->update('NetworkConfiguration', 'Id = 3', array('Changed' => 1));
537 //flush();
538 //$this->GenerateBills();
539 }
540 return($Output);
541 }
542
543 function SendPaymentEmail($MemberId, $FileId = '')
544 {
545 global $Config;
546
547 $DbResult = $this->Database->select('Member', '*', 'Id='.$MemberId);
548 $Member = $DbResult->fetch_assoc();
549
550 $DbResult = $this->Database->select('MemberPayment', '*', 'Member='.$MemberId);
551 $MemberPayment = $DbResult->fetch_assoc();
552
553 $DbResult = $this->Database->select('Subject', 'Name', 'Id='.$Member['Subject']);
554 $Subject = $DbResult->fetch_assoc();
555
556 $DbResult = $this->Database->select('User', '*', 'Id='.$Member['ResponsibleUser']);
557 $User = $DbResult->fetch_assoc();
558
559 $DbResult = $this->Database->select('Subject', '*', '`Id`='.$Config['Finance']['MainSubjectId']);
560 $MainSubject = $DbResult->fetch_assoc();
561
562 $DbResult = $this->Database->query('SELECT FinanceBankAccount.*, CONCAT(FinanceBankAccount.Number, "/", FinanceBank.Code) AS NumberFull FROM FinanceBankAccount '.
563 'JOIN FinanceBank ON FinanceBank.Id=FinanceBankAccount.Bank '.
564 'WHERE (FinanceBankAccount.`Subject`='.$Config['Finance']['MainSubjectId'].') AND (FinanceBankAccount.`Use`=1)');
565 $MainSubjectAccount = $DbResult->fetch_assoc();
566
567 if($User['Email'] != '')
568 {
569 $Title = 'Pravidelné vyúčtování služeb';
570 $Content = 'Vyúčtovaní zákazníka <strong>'.$Subject['Name'].'</strong> zastoupeného uživatelem <strong>'.$User['Name'].'</strong> ke dni <strong>'.$this->System->HumanDate(time()).'</strong>.<br /><br />'."\n".
571 'Váše aktuální služby: ';
572 $DbResult = $this->Database->query('SELECT GROUP_CONCAT(Service.Name) AS Name FROM ServiceCustomerRel LEFT JOIN Service '.
573 'ON Service.Id=ServiceCustomerRel.Service WHERE (ServiceCustomerRel.Customer='.$Member['Id'].') '.
574 'AND ServiceCustomerRel.Action IS NULL');
575 $Service = $DbResult->fetch_assoc();
576 $Content .= '<strong>'.$Service['Name'].'</strong><br />'."\n".
577 'Vaše platební období: <strong>'.$this->System->Modules['Finance']->BillingPeriods[$Member['BillingPeriod']]['Name'].'</strong><br />'."\n".
578 'Pravidelná platba za období: <strong>'.$MemberPayment['MonthlyTotal'].' Kč</strong><br />'."\n".
579 'Bankovní účet: <strong>'.$MainSubjectAccount['NumberFull'].'<br/>'."\n".
580 'Variabilní symbol: <strong>'.$Member['Subject'].'</strong><br/>'."\n".
581 'Stav vašeho účtu: <strong>'.($MemberPayment['Cash'] - $MemberPayment['MonthlyTotal']).' Kč</strong><br /><br />'."\n";
582 $Content .= 'Nové finanční operace:<br/>'.
583 '<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";
584 $DbResult = $this->Database->query('SELECT T1.* FROM ((SELECT Text, Time, Value AS Value, File FROM FinanceOperation WHERE (Subject='.$Member['Subject'].')) UNION ALL '.
585 '(SELECT Text, TimeCreation as Time, -Value as Value, File FROM FinanceInvoice WHERE (Subject='.$Member['Subject'].')) ORDER BY Time DESC) AS T1 WHERE (T1.Time > "'.$Member['BillingPeriodLastDate'].'")');
586 while($DbRow = $DbResult->fetch_assoc())
587 {
588 $Content .= '<tr><td style="border-style: solid; border-width: 1px; padding: 1px 5px 1px 5px; text-align: center;">'.HumanDate($DbRow['Time']).'</td>'.
589 '<td style="border-style: solid; border-width: 1px; padding: 1px 5px 1px 5px; text-align: center;">'.$DbRow['Text'].'</td>'.
590 '<td style="border-style: solid; border-width: 1px; padding: 1px 5px 1px 5px; text-align: center;">'.$DbRow['Value'].'</td></tr>'."\n";
591 }
592 $Content .= '</table><br />'."\n".
593 '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";
594
595 $Content .= '<br />Tento email je generován automaticky. V případě zjištění nesrovnalostí napište zpět.';
596 $this->System->AddEmailToQueue($User['Name'].' <'.$User['Email'].'>', $Title, $Content,
597 $Config['Web']['Admin'].' <'.$Config['Web']['AdminEmail'].'>');
598 $Output = '';
599 } else $Output = 'Uživatel '.$User['Name'].' nemá email.';
600 }
601
602 function GenerateBills()
603 {
604 $Output = '';
605
606 // FinanceInvoice
607 $DbResult = $this->Database->query('SELECT * FROM `FinanceInvoice` WHERE (`BillCode` <> "") '.
608 'AND (`Value` != 0) AND (`File` IS NULL)');
609 while($Row = $DbResult->fetch_assoc())
610 {
611 $DbResult2 = $this->Database->insert('File', array('Name' => '', 'Size' => 0,
612 'Directory' => $this->System->Modules['Finance']->DirectoryId, 'Time' => 'NOW()'));
613 $FileId = $this->Database->insert_id;
614 $FileName = 'doklad-'.$FileId.'.pdf';
615 $Bill = new BillInvoice();
616 $Bill->Database = &$this->System->Database;
617 $Bill->System = &$this->System;
618 $Bill->InvoiceId = $Row['Id'];
619 $FullFileName = $this->System->Modules['File']->GetDir($this->System->Modules['Finance']->DirectoryId).$FileName;
620 $Bill->SaveToFile($FullFileName);
621 if(file_exists($FullFileName))
622 {
623 $this->Database->update('File', 'Id='.$FileId, array('Name' => $FileName, 'Size' => filesize($FullFileName)));
624 $this->Database->update('FinanceInvoice', 'Id='.$Row['Id'], array('File' => $FileId));
625 $Output .= '.';
626 } else $Output .= 'Soubor "'.$FullFileName.'" se nepodařilo uložit.';
627 }
628
629 // FinanceOperations
630 return($Output);
631 }
632}
633
634$System->AddModule(new FinanceManagePage());
635$System->Modules['FinanceManagePage']->GetOutput();
636
637?>
Note: See TracBrowser for help on using the repository browser.