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

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