source: trunk/Modules/Finance/Manage.php@ 521

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