source: trunk/Modules/Finance/FinanceModels.php

Last change on this file was 968, checked in by chronos, 19 months ago
  • Modified: Improved content of email with finance account state.
  • Added: Customer IS action to generate finance email into email queue.
File size: 12.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 public int $PayingUsers;
37
38 function LoadMonthParameters(int $Period = 1) // 0 - now, 1 - next month
39 {
40 $DbResult = $this->Database->query('SELECT * FROM `FinanceBillingPeriod`');
41 while ($BillingPeriod = $DbResult->fetch_assoc())
42 {
43 $this->BillingPeriods[$BillingPeriod['Id']] = $BillingPeriod;
44 }
45
46 // Period parameter is not used as it have to be determined from item replacement
47 $DbResult = $this->Database->query('SELECT * FROM `FinanceCharge` WHERE (`ChangeAction` IS NULL) LIMIT 1');
48 $Row = $DbResult->fetch_array();
49 $this->kWh = $Row['kWh'];
50 $this->Internet = $Row['Internet'];
51 $this->Sprava = $Row['AdministrationPerUser'];
52 $this->RealMaxSpeed = $Row['InternetSpeed'];
53 $this->SpeedReserve = $Row['InternetSpeedReserve'];
54 $this->BaseSpeedElement = $Row['BaseSpeedElement'];
55 $this->MaxSpeed = $this->RealMaxSpeed - $this->SpeedReserve;
56 $this->TopTariffPrice = $Row['TopTariffPrice'];
57 $this->BaseTariffPrice = $Row['BaseTariffPrice'];
58
59 $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Member`');
60 $Row = $DbResult->fetch_row();
61 $this->InternetUsers = $Row[0];
62 $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Member` WHERE (`Blocked`=0) AND (`BillingPeriod` > 1)');
63 $Row = $DbResult->fetch_row();
64 $this->PayingUsers = $Row[0];
65
66 $this->SpravaUsers = $this->PayingUsers;
67
68 $DbResult = $this->Database->query('SELECT SUM(`MemberPayment`.`MonthlyInternet`) AS `MonthlyInternet`, '.
69 'SUM(`MemberPayment`.`MonthlyTotal`) AS `MonthlyTotal` '.
70 'FROM `MemberPayment` JOIN `Member` ON `Member`.`Id`=`MemberPayment`.`Member` WHERE `Member`.`Blocked`=0');
71 $Row = $DbResult->fetch_assoc();
72 $this->TotalInternetPaid = $Row['MonthlyInternet'];
73 $this->TotalPaid = $Row['MonthlyTotal'];
74 $this->Rounding = $this->System->Config['Finance']['Rounding'];
75 }
76
77 function W2Kc($Spotreba): string
78 {
79 return round($Spotreba * 0.72 * $this->kWh);
80 }
81
82 function GetFinanceGroupById(string $Id, string $Table): ?array
83 {
84 $DbResult = $this->Database->query('SELECT * FROM `'.$Table.'` WHERE `Id`= '.$Id);
85 if ($DbResult->num_rows == 1)
86 {
87 $Group = $DbResult->fetch_assoc();
88 return $Group;
89 }
90 echo('Finance group id '.$Id.' not found in table '.$Table);
91 return null;
92 }
93
94 function RecalculateMemberPayment(): string
95 {
96 $Output = 'Aktualizuji finance členů...<br />';
97 $this->Database->query('TRUNCATE TABLE `MemberPayment`');
98 $DbResult = $this->Database->query('SELECT * FROM `Member`');
99 while ($Member = $DbResult->fetch_assoc())
100 {
101 $DbResult2 = $this->Database->query('SELECT ((SELECT COALESCE(SUM(`Value`), 0) FROM `FinanceOperation` '.
102 'WHERE `Subject`='.$Member['Subject'].') - (SELECT COALESCE(SUM(`Value`), 0) FROM `FinanceInvoice` '.
103 'WHERE `Subject`='.$Member['Subject'].')) AS `Cash`');
104 $Cash = $DbResult2->fetch_row();
105 $Cash = $Cash[0];
106
107 $DbResult2 = $this->Database->query('SELECT SUM(`Product`.`Consumption`) * `StockSerialNumber`.`Amount` '.
108 'FROM `StockSerialNumber` JOIN `Product` ON `Product`.`Id` = `StockSerialNumber`.`Product` '.
109 'WHERE (`StockSerialNumber`.`Location` = '.$Member['Id'].') AND (`StockSerialNumber`.`TimeElimination` IS NULL)');
110 $ConsumptionPlus = $DbResult2->fetch_row();
111 $ConsumptionPlus = $ConsumptionPlus[0];
112
113 $DbResult2 = $this->Database->query('SELECT SUM(`Service`.`Price`) AS `Price` '.
114 'FROM `ServiceCustomerRel` LEFT JOIN '.
115 '`Service` ON `Service`.`Id` = `ServiceCustomerRel`.`Service` WHERE (`ServiceCustomerRel`.`Customer`='.
116 $Member['Id'].') AND (`ServiceCustomerRel`.`ChangeAction` IS NULL)');
117 $DbRow = $DbResult2->fetch_assoc();
118 $Monthly = 0;
119 if ($DbRow['Price'] != '') $MonthlyInet = $DbRow['Price'];
120 else $MonthlyInet = 0;
121
122 $Monthly += $MonthlyInet;
123 //$Monthly -= $this->W2Kc($ConsumptionPlus);
124 $Monthly = round($Monthly);
125
126 if ($Member['BillingPeriod'] == 1)
127 {
128 // Inactive payer
129 $MonthlyInet = 0;
130 $Monthly = 0;
131 $ConsumptionPlus = 0;
132 $Consumption = 0;
133 }
134 $Consumption = 0;
135 $this->Database->insert('MemberPayment', array('Member' => $Member['Id'],
136 'MonthlyInternet' => $MonthlyInet,
137 'MonthlyTotal' => $Monthly, 'MonthlyConsumption' => $this->W2Kc($Consumption),
138 'Cash' => $Cash, 'MonthlyPlus' => $this->W2Kc($ConsumptionPlus)));
139 }
140 ModuleLog::Cast($this->System->GetModule('Log'))->NewRecord('Finance', 'RecalculateMemberPayment');
141 return $Output;
142 }
143
144 function GetVATByType(string $TypeId): string
145 {
146 $Time = time();
147 $DbResult = $this->Database->select('FinanceVAT', 'Value', '(Type='.$TypeId.
148 ') AND (ValidFrom <= "'.TimeToMysqlDate($Time).'") AND ((ValidTo >= "'.
149 TimeToMysqlDate($Time).'") OR (ValidTo IS NULL)) LIMIT 1');
150 $Row = $DbResult->fetch_array();
151 return $Row[0];
152 }
153}
154
155class FinanceGroup extends Model
156{
157 static function GetModelDesc(): ModelDesc
158 {
159 $Desc = new ModelDesc(self::GetClassName());
160 $Desc->AddString('Description');
161 return $Desc;
162 }
163}
164
165class FinanceOperation extends Model
166{
167 static function GetModelDesc(): ModelDesc
168 {
169 $Desc = new ModelDesc(self::GetClassName());
170 $Desc->AddReference('Group', FinanceGroup::GetClassName());
171 $Desc->AddDateTime('Time');
172 $Desc->AddReference('Subject', Subject::GetClassName());
173 $Desc->AddBoolean('Cash');
174 $Desc->AddFloat('Value');
175 $Desc->AddFloat('ValueUser');
176 $Desc->AddReference('BillCode', DocumentLineCode::GetClassName());
177 $Desc->AddBoolean('Taxable');
178 $Desc->AddReference('File', 'File');
179 $Desc->AddString('Text');
180 $Desc->AddBoolean('Network');
181 $Desc->AddReference('BankAccount', FinanceBankAccount::GetClassName());
182 $Desc->AddReference('Treasury', FinanceTreasury::GetClassName());
183 $Desc->AddBoolean('Generate');
184 return $Desc;
185 }
186}
187
188class FinanceOperationGroup extends Model
189{
190 static function GetModelDesc(): ModelDesc
191 {
192 $Desc = new ModelDesc(self::GetClassName());
193 $Desc->AddString('Name');
194 $Desc->AddReference('DocumentLine', DocumentLine::GetClassName());
195 $Desc->AddInteger('ValueSign');
196 $Desc->AddInteger('Direction');
197 return $Desc;
198 }
199}
200
201class FinanceInvoice extends Model
202{
203 static function GetModelDesc(): ModelDesc
204 {
205 $Desc = new ModelDesc(self::GetClassName());
206 $Desc->AddReference('Group', FinanceGroup::GetClassName());
207 $Desc->AddReference('BillCode', DocumentLineCode::GetClassName());
208 $Desc->AddReference('Subject', Subject::GetClassName());
209 $Desc->AddDateTime('Time');
210 $Desc->AddDateTime('TimeDue');
211 $Desc->AddDateTime('TimePayment');
212 $Desc->AddFloat('Value');
213 $Desc->AddReference('File', File::GetClassName());
214 $Desc->AddDate('PeriodFrom');
215 $Desc->AddDate('PeriodTo');
216 $Desc->AddBoolean('Cash');
217 $Desc->AddBoolean('Generate');
218 $Desc->AddBoolean('VisibleToUser');
219 return $Desc;
220 }
221}
222
223class FinanceInvoiceGroup extends Model
224{
225 static function GetModelDesc(): ModelDesc
226 {
227 $Desc = new ModelDesc(self::GetClassName());
228 $Desc->AddString('Name');
229 $Desc->AddReference('DocumentLine', DocumentLine::GetClassName());
230 $Desc->AddInteger('ValueSign');
231 $Desc->AddInteger('Direction');
232 return $Desc;
233 }
234}
235
236class Company extends Model
237{
238 static function GetModelDesc(): ModelDesc
239 {
240 $Desc = new ModelDesc(self::GetClassName());
241 $Desc->AddString('Name');
242 $Desc->AddReference('Subject', Subject::GetClassName());
243 return $Desc;
244 }
245}
246
247class FinanceInvoiceItem extends Model
248{
249 static function GetModelDesc(): ModelDesc
250 {
251 $Desc = new ModelDesc(self::GetClassName());
252 $Desc->AddReference('FinanceInvoice', FinanceInvoice::GetClassName());
253 $Desc->AddString('Description');
254 $Desc->AddFloat('Price');
255 $Desc->AddFloat('Quantity');
256 $Desc->AddInteger('VAT');
257 return $Desc;
258 }
259}
260
261class FinanceTreasury extends Model
262{
263 static function GetModelDesc(): ModelDesc
264 {
265 $Desc = new ModelDesc(self::GetClassName());
266 $Desc->AddDate('TimeCreate');
267 $Desc->AddString('Name');
268 return $Desc;
269 }
270}
271
272class FinanceTreasuryCheck extends Model
273{
274 static function GetModelDesc(): ModelDesc
275 {
276 $Desc = new ModelDesc(self::GetClassName());
277 $Desc->AddReference('Treasury', FinanceTreasury::GetClassName(), false);
278 $Desc->AddDateTime('Time');
279 $Desc->AddInteger('Value1');
280 $Desc->AddInteger('Value2');
281 $Desc->AddInteger('Value5');
282 $Desc->AddInteger('Value10');
283 $Desc->AddInteger('Value20');
284 $Desc->AddInteger('Value50');
285 $Desc->AddInteger('Value100');
286 $Desc->AddInteger('Value200');
287 $Desc->AddInteger('Value500');
288 $Desc->AddInteger('Value1000');
289 $Desc->AddInteger('Value2000');
290 $Desc->AddInteger('Value5000');
291 return $Desc;
292 }
293}
294
295class FinanceBankAccount extends Model
296{
297 static function GetModelDesc(): ModelDesc
298 {
299 $Desc = new ModelDesc(self::GetClassName());
300 $Desc->AddReference('Subject', Subject::GetClassName());
301 $Desc->AddString('Comment');
302 $Desc->AddString('Number');
303 $Desc->AddReference('Bank', FinanceBank::GetClassName());
304 $Desc->AddDate('TimeCreate');
305 $Desc->AddDate('TimeEnd');
306 $Desc->AddReference('Currency', Currency::GetClassName());
307 $Desc->AddString('LoginName');
308 $Desc->AddString('LoginPassword');
309 $Desc->AddBoolean('Use');
310 $Desc->AddDate('LastImportDate');
311 $Desc->AddString('LastImportId');
312 $Desc->AddBoolean('AutoImport');
313 return $Desc;
314 }
315}
316
317class FinanceBank extends Model
318{
319 static function GetModelDesc(): ModelDesc
320 {
321 $Desc = new ModelDesc(self::GetClassName());
322 $Desc->AddString('Name');
323 $Desc->AddString('Code');
324 $Desc->AddString('BIC');
325 $Desc->AddReference('Country', Country::GetClassName());
326 return $Desc;
327 }
328}
329
330class Currency extends Model
331{
332 static function GetModelDesc(): ModelDesc
333 {
334 $Desc = new ModelDesc(self::GetClassName());
335 $Column = $Desc->AddString('Code');
336 $Column->MaxLength = 3;
337 $Desc->AddString('Name');
338 $Desc->AddString('Symbol');
339 return $Desc;
340 }
341}
342
343class FinanceCharge extends Model
344{
345 static function GetModelDesc(): ModelDesc
346 {
347 $Desc = new ModelDesc(self::GetClassName());
348 $Desc->AddInteger('Internet');
349 $Desc->AddInteger('InternetSpeed');
350 $Desc->AddInteger('InternetSpeedReserve');
351 $Desc->AddInteger('AdministrationPerUser');
352 $Desc->AddInteger('kWh');
353 $Desc->AddInteger('BaseSpeedElement');
354 $Desc->AddInteger('BaseTariffPrice');
355 $Desc->AddInteger('TopTariffPrice');
356 $Desc->AddChangeAction();
357 return $Desc;
358 }
359}
360
361class FinanceVat extends Model
362{
363 static function GetModelDesc(): ModelDesc
364 {
365 $Desc = new ModelDesc(self::GetClassName());
366 $Desc->AddReference('Type', FinanceVatType::GetClassName());
367 $Desc->AddDate('ValidFrom');
368 $Desc->AddDate('ValidTo');
369 $Desc->AddInteger('Value');
370 return $Desc;
371 }
372}
373
374class FinanceVatType extends Model
375{
376 static function GetModelDesc(): ModelDesc
377 {
378 $Desc = new ModelDesc(self::GetClassName());
379 $Desc->AddString('Name');
380 return $Desc;
381 }
382}
383
384class FinanceBillingPeriod extends Model
385{
386 static function GetModelDesc(): ModelDesc
387 {
388 $Desc = new ModelDesc(self::GetClassName());
389 $Desc->AddString('Name');
390 $Desc->AddInteger('MonthCount');
391 return $Desc;
392 }
393}
394
395class FinanceInvoiceOperationRel extends Model
396{
397 static function GetModelDesc(): ModelDesc
398 {
399 $Desc = new ModelDesc(self::GetClassName());
400 $Desc->AddReference('Invoice', FinanceInvoice::GetClassName());
401 $Desc->AddReference('Operation', FinanceOperation::GetClassName());
402 return $Desc;
403 }
404}
Note: See TracBrowser for help on using the repository browser.