source: trunk/Modules/Finance/FinanceModels.php@ 899

Last change on this file since 899 was 899, checked in by chronos, 4 years ago
File size: 15.5 KB
Line 
1<?php
2
3// TODO: Move constants to Finance module configuration
4define('TARIFF_FREE', 7);
5define('INVOICE_DUE_DAYS', 15);
6define('OPERATION_GROUP_TREASURY_IN', 1);
7define('OPERATION_GROUP_TREASURY_OUT', 2);
8define('OPERATION_GROUP_ACCOUNT_IN', 3);
9define('OPERATION_GROUP_ACCOUNT_OUT', 4);
10define('INVOICE_GROUP_IN', 1);
11define('INVOICE_GROUP_OUT', 2);
12define('VAT_TYPE_BASE', 2);
13define('FINANCE_DIRECTION_OUT', 1);
14define('FINANCE_DIRECTION_IN', 0);
15
16class Finance extends Model
17{
18 public string $kWh;
19 public string $Internet;
20 public string $Sprava;
21 public string $DatumOdecteni;
22 public string $InternetUsers;
23 public string $SpravaUsers;
24 public string $MaxSpeed;
25 public string $RealMaxSpeed;
26 public string $SpeedReserve;
27 public string $BaseSpeedElement;
28 public string $BaseTariffPrice;
29 public string $TopTariffPrice;
30 public string $TotalPaid;
31 public string $TotalInternetPaid;
32 public string $MainSubject;
33 public array $BillingPeriods;
34 public string $DirectoryId;
35 public string $Rounding;
36
37 function LoadMonthParameters(int $Period = 1) // 0 - now, 1 - next month
38 {
39 $DbResult = $this->Database->query('SELECT * FROM `FinanceBillingPeriod`');
40 while ($BillingPeriod = $DbResult->fetch_assoc())
41 $this->BillingPeriods[$BillingPeriod['Id']] = $BillingPeriod;
42
43 // Period parameter is not used as it have to be determined from item replacement
44 $DbResult = $this->Database->query('SELECT * FROM `FinanceCharge` WHERE (`ChangeAction` IS NULL) LIMIT 1');
45 $Row = $DbResult->fetch_array();
46 $this->kWh = $Row['kWh'];
47 $this->Internet = $Row['Internet'];
48 $this->Sprava = $Row['AdministrationPerUser'];
49 $this->RealMaxSpeed = $Row['InternetSpeed'];
50 $this->SpeedReserve = $Row['InternetSpeedReserve'];
51 $this->BaseSpeedElement = $Row['BaseSpeedElement'];
52 $this->MaxSpeed = $this->RealMaxSpeed - $this->SpeedReserve;
53 $this->TopTariffPrice = $Row['TopTariffPrice'];
54 $this->BaseTariffPrice = $Row['BaseTariffPrice'];
55
56 $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Member`');
57 $Row = $DbResult->fetch_row();
58 $this->InternetUsers = $Row[0];
59 $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Member` WHERE (`Blocked`=0) AND (`BillingPeriod` > 1)');
60 $Row = $DbResult->fetch_row();
61 $this->PayingUsers = $Row[0];
62
63 $this->SpravaUsers = $this->PayingUsers;
64
65 $DbResult = $this->Database->query('SELECT SUM(`MemberPayment`.`MonthlyInternet`) AS `MonthlyInternet`, '.
66 'SUM(`MemberPayment`.`MonthlyTotal`) AS `MonthlyTotal` '.
67 'FROM `MemberPayment` JOIN `Member` ON `Member`.`Id`=`MemberPayment`.`Member` WHERE `Member`.`Blocked`=0');
68 $Row = $DbResult->fetch_assoc();
69 $this->TotalInternetPaid = $Row['MonthlyInternet'];
70 $this->TotalPaid = $Row['MonthlyTotal'];
71 $this->Rounding = $this->System->Config['Finance']['Rounding'];
72 }
73
74 function W2Kc($Spotreba): string
75 {
76 return round($Spotreba * 0.72 * $this->kWh);
77 }
78
79 function CreateFinanceYear(int $Year)
80 {
81 $StartTime = mktime(0, 0, 0, 1, 1, $Year);
82 $EndTime = mktime(0, 0, 0, 12, 31, $Year);
83 $this->Database->insert('FinanceYear', array('Year' => $Year,
84 'DateStart' => TimeToMysqlDate($StartTime), 'DateEnd' => TimeToMysqlDate($EndTime), 'Closed' => 0));
85 $YearId = $this->Database->insert_id;
86
87 // Create DocumentLineSequence from previous
88 $DbResult = $this->Database->select('DocumentLine', 'Id', '`Yearly` = 1');
89 while ($DbRow = $DbResult->fetch_assoc())
90 {
91 $this->Database->insert('DocumentLineSequence', array('FinanceYear' => $YearId,
92 'NextNumber' => 1, 'YearPrefix' => 1, 'DocumentLine' => $DbRow['Id']));
93 }
94 }
95
96 function GetFinanceYear(int $Year): array
97 {
98 if ($Year == 0)
99 {
100 // Get latest year
101 $DbResult = $this->Database->select('FinanceYear', '*', '1 ORDER BY `Year` DESC LIMIT 1');
102 } else $DbResult = $this->Database->select('FinanceYear', '*', '`Year`='.$Year);
103 if ($DbResult->num_rows == 0)
104 {
105 if ($Year == date('Y'))
106 {
107 $this->CreateFinanceYear($Year);
108 $DbResult = $this->Database->select('FinanceYear', '*', '`Year`='.$Year);
109 } else throw new Exception('Rok '.$Year.' nenalezen');
110 }
111 $FinanceYear = $DbResult->fetch_assoc();
112 if ($FinanceYear['Closed'] == 1)
113 throw new Exception('Rok '.$FinanceYear['Year'].' je již uzavřen. Nelze do něj přidávat položky.');
114 return $FinanceYear;
115 }
116
117 function GetNextDocumentLineNumber(string $Id, int $FinanceYear = 0): string
118 {
119 $FinanceYear = $this->GetFinanceYear($FinanceYear);
120
121 $DbResult = $this->Database->query('SELECT `Shortcut`, `Id` FROM `DocumentLine` WHERE `Id`='.$Id);
122 $DocumentLine = $DbResult->fetch_assoc();
123
124 $DbResult = $this->Database->query('SELECT * FROM `DocumentLineSequence` WHERE '.
125 '`DocumentLine`='.$Id.' AND `FinanceYear`='.$FinanceYear['Id']);
126 $Sequence = $DbResult->fetch_assoc();
127
128 if ($Sequence['YearPrefix'] == 1)
129 {
130 $Result = $DocumentLine['Shortcut'].$Sequence['NextNumber'].'/'.$FinanceYear['Year'];
131 } else $Result = $DocumentLine['Shortcut'].$Sequence['NextNumber'];
132
133 $this->Database->query('UPDATE `DocumentLineSequence` SET `NextNumber` = `NextNumber` + 1 '.
134 'WHERE (`DocumentLine`='.$Id.') AND (`FinanceYear`='.$FinanceYear['Id'].')');
135 return $Result;
136 }
137
138 function GetNextDocumentLineNumberId(string $Id, int $FinanceYear = 0): int
139 {
140 $Code = $this->GetNextDocumentLineNumber($Id, $FinanceYear);
141 $this->Database->insert('DocumentLineCode', array('DocumentLine' => $Id, 'Name' => $Code));
142 return $this->Database->insert_id;
143 }
144
145 function GetFinanceGroupById(string $Id, string $Table): ?array
146 {
147 $DbResult = $this->Database->query('SELECT * FROM `'.$Table.'` WHERE `Id`= '.$Id);
148 if ($DbResult->num_rows == 1) {
149 $Group = $DbResult->fetch_assoc();
150 return $Group;
151 }
152 echo('Finance group id '.$Id.' not found in table '.$Table);
153 return null;
154 }
155
156 function RecalculateMemberPayment(): string
157 {
158 $Output = 'Aktualizuji finance členů...<br />';
159 $this->Database->query('TRUNCATE TABLE `MemberPayment`');
160 $DbResult = $this->Database->query('SELECT * FROM `Member`');
161 while ($Member = $DbResult->fetch_assoc())
162 {
163 $DbResult2 = $this->Database->query('SELECT ((SELECT COALESCE(SUM(`Value`), 0) FROM `FinanceOperation` '.
164 'WHERE `Subject`='.$Member['Subject'].') - (SELECT COALESCE(SUM(`Value`), 0) FROM `FinanceInvoice` '.
165 'WHERE `Subject`='.$Member['Subject'].')) AS `Cash`');
166 $Cash = $DbResult2->fetch_row();
167 $Cash = $Cash[0];
168
169 $DbResult2 = $this->Database->query('SELECT SUM(`Product`.`Consumption`) * `StockSerialNumber`.`Amount` '.
170 'FROM `StockSerialNumber` JOIN `Product` ON `Product`.`Id` = `StockSerialNumber`.`Product` '.
171 'WHERE (`StockSerialNumber`.`Location` = '.$Member['Id'].') AND (`StockSerialNumber`.`TimeElimination` IS NULL)');
172 $ConsumptionPlus = $DbResult2->fetch_row();
173 $ConsumptionPlus = $ConsumptionPlus[0];
174
175 $DbResult2 = $this->Database->query('SELECT SUM(`Service`.`Price`) AS `Price` '.
176 'FROM `ServiceCustomerRel` LEFT JOIN '.
177 '`Service` ON `Service`.`Id` = `ServiceCustomerRel`.`Service` WHERE (`ServiceCustomerRel`.`Customer`='.
178 $Member['Id'].') AND (`ServiceCustomerRel`.`ChangeAction` IS NULL)');
179 $DbRow = $DbResult2->fetch_assoc();
180 $Monthly = 0;
181 if ($DbRow['Price'] != '') $MonthlyInet = $DbRow['Price'];
182 else $MonthlyInet = 0;
183
184 $Monthly += $MonthlyInet;
185 //$Monthly -= $this->W2Kc($ConsumptionPlus);
186 $Monthly = round($Monthly);
187
188 if ($Member['BillingPeriod'] == 1)
189 {
190 // Inactive payer
191 $MonthlyInet = 0;
192 $Monthly = 0;
193 $ConsumptionPlus = 0;
194 $Consumption = 0;
195 }
196 $Consumption = 0;
197 $this->Database->insert('MemberPayment', array('Member' => $Member['Id'],
198 'MonthlyInternet' => $MonthlyInet,
199 'MonthlyTotal' => $Monthly, 'MonthlyConsumption' => $this->W2Kc($Consumption),
200 'Cash' => $Cash, 'MonthlyPlus' => $this->W2Kc($ConsumptionPlus)));
201 }
202 ModuleLog::Cast($this->System->GetModule('Log'))->NewRecord('Finance', 'RecalculateMemberPayment');
203 return $Output;
204 }
205
206 function GetVATByType(string $TypeId): string
207 {
208 $Time = time();
209 $DbResult = $this->Database->select('FinanceVAT', 'Value', '(Type='.$TypeId.
210 ') AND (ValidFrom <= "'.TimeToMysqlDate($Time).'") AND ((ValidTo >= "'.
211 TimeToMysqlDate($Time).'") OR (ValidTo IS NULL)) LIMIT 1');
212 $Row = $DbResult->fetch_array();
213 return $Row[0];
214 }
215}
216
217class FinanceGroup extends Model
218{
219 static function GetModelDesc(): ModelDesc
220 {
221 $Desc = new ModelDesc(self::GetClassName());
222 $Desc->AddString('Description');
223 return $Desc;
224 }
225}
226
227class FinanceOperation extends Model
228{
229 static function GetModelDesc(): ModelDesc
230 {
231 $Desc = new ModelDesc(self::GetClassName());
232 $Desc->AddReference('Group', FinanceGroup::GetClassName());
233 $Desc->AddDateTime('Time');
234 $Desc->AddReference('Subject', Subject::GetClassName());
235 $Desc->AddBoolean('Cash');
236 $Desc->AddFloat('Value');
237 $Desc->AddFloat('ValueUser');
238 $Desc->AddReference('BillCode', DocumentLineCode::GetClassName());
239 $Desc->AddBoolean('Taxable');
240 $Desc->AddReference('File', 'File');
241 $Desc->AddString('Text');
242 $Desc->AddBoolean('Network');
243 $Desc->AddReference('BankAccount', FinanceBankAccount::GetClassName());
244 $Desc->AddReference('Treasury', FinanceTreasury::GetClassName());
245 $Desc->AddBoolean('Generate');
246 return $Desc;
247 }
248}
249
250class FinanceOperationGroup extends Model
251{
252 static function GetModelDesc(): ModelDesc
253 {
254 $Desc = new ModelDesc(self::GetClassName());
255 $Desc->AddString('Name');
256 $Desc->AddReference('DocumentLine', DocumentLine::GetClassName());
257 $Desc->AddInteger('ValueSign');
258 $Desc->AddInteger('Direction');
259 return $Desc;
260 }
261}
262
263class FinanceInvoice extends Model
264{
265 static function GetModelDesc(): ModelDesc
266 {
267 $Desc = new ModelDesc(self::GetClassName());
268 $Desc->AddReference('Group', FinanceGroup::GetClassName());
269 $Desc->AddReference('BillCode', DocumentLineCode::GetClassName());
270 $Desc->AddReference('Subject', Subject::GetClassName());
271 $Desc->AddDateTime('Time');
272 $Desc->AddDateTime('TimeDue');
273 $Desc->AddDateTime('TimePayment');
274 $Desc->AddFloat('Value');
275 $Desc->AddReference('File', File::GetClassName());
276 $Desc->AddDate('PeriodFrom');
277 $Desc->AddDate('PeriodTo');
278 $Desc->AddBoolean('Cash');
279 $Desc->AddBoolean('Generate');
280 $Desc->AddBoolean('VisibleToUser');
281 return $Desc;
282 }
283}
284
285class FinanceInvoiceGroup extends Model
286{
287 static function GetModelDesc(): ModelDesc
288 {
289 $Desc = new ModelDesc(self::GetClassName());
290 $Desc->AddString('Name');
291 $Desc->AddReference('DocumentLine', DocumentLine::GetClassName());
292 $Desc->AddInteger('ValueSign');
293 $Desc->AddInteger('Direction');
294 return $Desc;
295 }
296}
297
298class Company extends Model
299{
300 static function GetModelDesc(): ModelDesc
301 {
302 $Desc = new ModelDesc(self::GetClassName());
303 $Desc->AddString('Name');
304 $Desc->AddReference('Subject', Subject::GetClassName());
305 return $Desc;
306 }
307}
308
309class FinanceInvoiceItem extends Model
310{
311 static function GetModelDesc(): ModelDesc
312 {
313 $Desc = new ModelDesc(self::GetClassName());
314 $Desc->AddReference('FinanceInvoice', FinanceInvoice::GetClassName());
315 $Desc->AddString('Description');
316 $Desc->AddFloat('Price');
317 $Desc->AddFloat('Quantity');
318 $Desc->AddInteger('VAT');
319 return $Desc;
320 }
321}
322
323class FinanceTreasury extends Model
324{
325 static function GetModelDesc(): ModelDesc
326 {
327 $Desc = new ModelDesc(self::GetClassName());
328 $Desc->AddString('Name');
329 $Desc->AddDate('TimeCreate');
330 return $Desc;
331 }
332}
333
334class FinanceTreasuryCheck extends Model
335{
336 static function GetModelDesc(): ModelDesc
337 {
338 $Desc = new ModelDesc(self::GetClassName());
339 $Desc->AddReference('Treasury', FinanceTreasury::GetClassName(), false);
340 $Desc->AddDateTime('Time');
341 $Desc->AddInteger('Value1');
342 $Desc->AddInteger('Value2');
343 $Desc->AddInteger('Value5');
344 $Desc->AddInteger('Value10');
345 $Desc->AddInteger('Value20');
346 $Desc->AddInteger('Value50');
347 $Desc->AddInteger('Value100');
348 $Desc->AddInteger('Value200');
349 $Desc->AddInteger('Value500');
350 $Desc->AddInteger('Value1000');
351 $Desc->AddInteger('Value2000');
352 $Desc->AddInteger('Value5000');
353 return $Desc;
354 }
355}
356
357class FinanceBankAccount extends Model
358{
359 static function GetModelDesc(): ModelDesc
360 {
361 $Desc = new ModelDesc(self::GetClassName());
362 $Desc->AddReference('Subject', Subject::GetClassName());
363 $Desc->AddString('Comment');
364 $Desc->AddString('Number');
365 $Desc->AddReference('Bank', FinanceBank::GetClassName());
366 $Desc->AddDate('TimeCreate');
367 $Desc->AddDate('TimeEnd');
368 $Desc->AddReference('Currency', Currency::GetClassName());
369 $Desc->AddString('LoginName');
370 $Desc->AddString('LoginPassword');
371 $Desc->AddBoolean('Use');
372 $Desc->AddDate('LastImportDate');
373 $Desc->AddString('LastImportId');
374 $Desc->AddBoolean('AutoImport');
375 return $Desc;
376 }
377}
378
379class FinanceBank extends Model
380{
381 static function GetModelDesc(): ModelDesc
382 {
383 $Desc = new ModelDesc(self::GetClassName());
384 $Desc->AddString('Name');
385 $Desc->AddString('Code');
386 $Desc->AddString('BIC');
387 $Desc->AddReference('Country', Country::GetClassName());
388 return $Desc;
389 }
390}
391
392class Currency extends Model
393{
394 static function GetModelDesc(): ModelDesc
395 {
396 $Desc = new ModelDesc(self::GetClassName());
397 $Desc->AddString('Code');
398 $Desc->AddString('Name');
399 $Desc->AddString('Symbol');
400 return $Desc;
401 }
402}
403
404class FinanceCharge extends Model
405{
406 static function GetModelDesc(): ModelDesc
407 {
408 $Desc = new ModelDesc(self::GetClassName());
409 $Desc->AddInteger('Internet');
410 $Desc->AddInteger('InternetSpeed');
411 $Desc->AddInteger('InternetSpeedReserve');
412 $Desc->AddInteger('AdministrationPerUser');
413 $Desc->AddInteger('kWh');
414 $Desc->AddInteger('BaseSpeedElement');
415 $Desc->AddInteger('BaseTariffPrice');
416 $Desc->AddInteger('TopTariffPrice');
417 $Desc->AddChangeAction();
418 return $Desc;
419 }
420}
421
422class FinanceVat extends Model
423{
424 static function GetModelDesc(): ModelDesc
425 {
426 $Desc = new ModelDesc(self::GetClassName());
427 $Desc->AddReference('Type', FinanceVatType::GetClassName());
428 $Desc->AddDate('ValidFrom');
429 $Desc->AddDate('ValidTo');
430 $Desc->AddInteger('Value');
431 return $Desc;
432 }
433}
434
435class FinanceVatType extends Model
436{
437 static function GetModelDesc(): ModelDesc
438 {
439 $Desc = new ModelDesc(self::GetClassName());
440 $Desc->AddString('Name');
441 return $Desc;
442 }
443}
444
445class Contract extends Model
446{
447 static function GetModelDesc(): ModelDesc
448 {
449 $Desc = new ModelDesc(self::GetClassName());
450 $Desc->AddReference('DocumentLine', DocumentLine::GetClassName());
451 $Desc->AddReference('BillCode', DocumentLineCode::GetClassName());
452 $Desc->AddReference('Subject', Subject::GetClassName());
453 $Desc->AddDate('ValidFrom');
454 $Desc->AddDate('ValidTo');
455 $Desc->AddReference('File', File::GetClassName());
456 return $Desc;
457 }
458}
459
460class FinanceBillingPeriod extends Model
461{
462 static function GetModelDesc(): ModelDesc
463 {
464 $Desc = new ModelDesc(self::GetClassName());
465 $Desc->AddString('Name');
466 $Desc->AddInteger('MonthCount');
467 return $Desc;
468 }
469}
470
471class FinanceInvoiceOperationRel extends Model
472{
473 static function GetModelDesc(): ModelDesc
474 {
475 $Desc = new ModelDesc(self::GetClassName());
476 $Desc->AddReference('Invoice', FinanceInvoice::GetClassName());
477 $Desc->AddReference('Operation', FinanceOperation::GetClassName());
478 return $Desc;
479 }
480}
Note: See TracBrowser for help on using the repository browser.