Database->query('SELECT * FROM `FinanceBillingPeriod`'); while ($BillingPeriod = $DbResult->fetch_assoc()) { $this->BillingPeriods[$BillingPeriod['Id']] = $BillingPeriod; } // Period parameter is not used as it have to be determined from item replacement $DbResult = $this->Database->query('SELECT * FROM `FinanceCharge` WHERE (`ChangeAction` IS NULL) LIMIT 1'); $Row = $DbResult->fetch_array(); $this->kWh = $Row['kWh']; $this->Internet = $Row['Internet']; $this->Sprava = $Row['AdministrationPerUser']; $this->RealMaxSpeed = $Row['InternetSpeed']; $this->SpeedReserve = $Row['InternetSpeedReserve']; $this->BaseSpeedElement = $Row['BaseSpeedElement']; $this->MaxSpeed = $this->RealMaxSpeed - $this->SpeedReserve; $this->TopTariffPrice = $Row['TopTariffPrice']; $this->BaseTariffPrice = $Row['BaseTariffPrice']; $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Member`'); $Row = $DbResult->fetch_row(); $this->InternetUsers = $Row[0]; $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Member` WHERE (`Blocked`=0) AND (`BillingPeriod` > 1)'); $Row = $DbResult->fetch_row(); $this->PayingUsers = $Row[0]; $this->SpravaUsers = $this->PayingUsers; $DbResult = $this->Database->query('SELECT SUM(`MemberPayment`.`MonthlyInternet`) AS `MonthlyInternet`, '. 'SUM(`MemberPayment`.`MonthlyTotal`) AS `MonthlyTotal` '. 'FROM `MemberPayment` JOIN `Member` ON `Member`.`Id`=`MemberPayment`.`Member` WHERE `Member`.`Blocked`=0'); $Row = $DbResult->fetch_assoc(); $this->TotalInternetPaid = $Row['MonthlyInternet']; $this->TotalPaid = $Row['MonthlyTotal']; $this->Rounding = $this->System->Config['Finance']['Rounding']; } function W2Kc($Spotreba): string { return round($Spotreba * 0.72 * $this->kWh); } function GetFinanceGroupById(string $Id, string $Table): ?array { $DbResult = $this->Database->query('SELECT * FROM `'.$Table.'` WHERE `Id`= '.$Id); if ($DbResult->num_rows == 1) { $Group = $DbResult->fetch_assoc(); return $Group; } echo('Finance group id '.$Id.' not found in table '.$Table); return null; } function RecalculateMemberPayment(): string { $Output = 'Aktualizuji finance členů...
'; $this->Database->query('TRUNCATE TABLE `MemberPayment`'); $DbResult = $this->Database->query('SELECT * FROM `Member`'); while ($Member = $DbResult->fetch_assoc()) { $DbResult2 = $this->Database->query('SELECT ((SELECT COALESCE(SUM(`Value`), 0) FROM `FinanceOperation` '. 'WHERE `Subject`='.$Member['Subject'].') - (SELECT COALESCE(SUM(`Value`), 0) FROM `FinanceInvoice` '. 'WHERE `Subject`='.$Member['Subject'].')) AS `Cash`'); $Cash = $DbResult2->fetch_row(); $Cash = $Cash[0]; $DbResult2 = $this->Database->query('SELECT SUM(`Product`.`Consumption`) * `StockSerialNumber`.`Amount` '. 'FROM `StockSerialNumber` JOIN `Product` ON `Product`.`Id` = `StockSerialNumber`.`Product` '. 'WHERE (`StockSerialNumber`.`Location` = '.$Member['Id'].') AND (`StockSerialNumber`.`TimeElimination` IS NULL)'); $ConsumptionPlus = $DbResult2->fetch_row(); $ConsumptionPlus = $ConsumptionPlus[0]; $DbResult2 = $this->Database->query('SELECT SUM(`Service`.`Price`) AS `Price` '. 'FROM `ServiceCustomerRel` LEFT JOIN '. '`Service` ON `Service`.`Id` = `ServiceCustomerRel`.`Service` WHERE (`ServiceCustomerRel`.`Customer`='. $Member['Id'].') AND (`ServiceCustomerRel`.`ChangeAction` IS NULL)'); $DbRow = $DbResult2->fetch_assoc(); $Monthly = 0; if ($DbRow['Price'] != '') $MonthlyInet = $DbRow['Price']; else $MonthlyInet = 0; $Monthly += $MonthlyInet; //$Monthly -= $this->W2Kc($ConsumptionPlus); $Monthly = round($Monthly); if ($Member['BillingPeriod'] == 1) { // Inactive payer $MonthlyInet = 0; $Monthly = 0; $ConsumptionPlus = 0; $Consumption = 0; } $Consumption = 0; $this->Database->insert('MemberPayment', array('Member' => $Member['Id'], 'MonthlyInternet' => $MonthlyInet, 'MonthlyTotal' => $Monthly, 'MonthlyConsumption' => $this->W2Kc($Consumption), 'Cash' => $Cash, 'MonthlyPlus' => $this->W2Kc($ConsumptionPlus))); } ModuleLog::Cast($this->System->GetModule('Log'))->NewRecord('Finance', 'RecalculateMemberPayment'); return $Output; } function GetVATByType(string $TypeId): string { $Time = time(); $DbResult = $this->Database->select('FinanceVAT', 'Value', '(Type='.$TypeId. ') AND (ValidFrom <= "'.TimeToMysqlDate($Time).'") AND ((ValidTo >= "'. TimeToMysqlDate($Time).'") OR (ValidTo IS NULL)) LIMIT 1'); $Row = $DbResult->fetch_array(); return $Row[0]; } } class FinanceGroup extends Model { static function GetModelDesc(): ModelDesc { $Desc = new ModelDesc(self::GetClassName()); $Desc->AddString('Description'); return $Desc; } } class FinanceOperation extends Model { static function GetModelDesc(): ModelDesc { $Desc = new ModelDesc(self::GetClassName()); $Desc->AddReference('Group', FinanceGroup::GetClassName()); $Desc->AddDateTime('Time'); $Desc->AddReference('Subject', Subject::GetClassName()); $Desc->AddBoolean('Cash'); $Desc->AddFloat('Value'); $Desc->AddFloat('ValueUser'); $Desc->AddReference('BillCode', DocumentLineCode::GetClassName()); $Desc->AddBoolean('Taxable'); $Desc->AddReference('File', 'File'); $Desc->AddString('Text'); $Desc->AddBoolean('Network'); $Desc->AddReference('BankAccount', FinanceBankAccount::GetClassName()); $Desc->AddReference('Treasury', FinanceTreasury::GetClassName()); $Desc->AddBoolean('Generate'); return $Desc; } } class FinanceOperationGroup extends Model { static function GetModelDesc(): ModelDesc { $Desc = new ModelDesc(self::GetClassName()); $Desc->AddString('Name'); $Desc->AddReference('DocumentLine', DocumentLine::GetClassName()); $Desc->AddInteger('ValueSign'); $Desc->AddInteger('Direction'); return $Desc; } } class FinanceInvoice extends Model { static function GetModelDesc(): ModelDesc { $Desc = new ModelDesc(self::GetClassName()); $Desc->AddReference('Group', FinanceGroup::GetClassName()); $Desc->AddReference('BillCode', DocumentLineCode::GetClassName()); $Desc->AddReference('Subject', Subject::GetClassName()); $Desc->AddDateTime('Time'); $Desc->AddDateTime('TimeDue'); $Desc->AddDateTime('TimePayment'); $Desc->AddFloat('Value'); $Desc->AddReference('File', File::GetClassName()); $Desc->AddDate('PeriodFrom'); $Desc->AddDate('PeriodTo'); $Desc->AddBoolean('Cash'); $Desc->AddBoolean('Generate'); $Desc->AddBoolean('VisibleToUser'); return $Desc; } } class FinanceInvoiceGroup extends Model { static function GetModelDesc(): ModelDesc { $Desc = new ModelDesc(self::GetClassName()); $Desc->AddString('Name'); $Desc->AddReference('DocumentLine', DocumentLine::GetClassName()); $Desc->AddInteger('ValueSign'); $Desc->AddInteger('Direction'); return $Desc; } } class Company extends Model { static function GetModelDesc(): ModelDesc { $Desc = new ModelDesc(self::GetClassName()); $Desc->AddString('Name'); $Desc->AddReference('Subject', Subject::GetClassName()); return $Desc; } } class FinanceInvoiceItem extends Model { static function GetModelDesc(): ModelDesc { $Desc = new ModelDesc(self::GetClassName()); $Desc->AddReference('FinanceInvoice', FinanceInvoice::GetClassName()); $Desc->AddString('Description'); $Desc->AddFloat('Price'); $Desc->AddFloat('Quantity'); $Desc->AddInteger('VAT'); return $Desc; } } class FinanceTreasury extends Model { static function GetModelDesc(): ModelDesc { $Desc = new ModelDesc(self::GetClassName()); $Desc->AddDate('TimeCreate'); $Desc->AddString('Name'); return $Desc; } } class FinanceTreasuryCheck extends Model { static function GetModelDesc(): ModelDesc { $Desc = new ModelDesc(self::GetClassName()); $Desc->AddReference('Treasury', FinanceTreasury::GetClassName(), false); $Desc->AddDateTime('Time'); $Desc->AddInteger('Value1'); $Desc->AddInteger('Value2'); $Desc->AddInteger('Value5'); $Desc->AddInteger('Value10'); $Desc->AddInteger('Value20'); $Desc->AddInteger('Value50'); $Desc->AddInteger('Value100'); $Desc->AddInteger('Value200'); $Desc->AddInteger('Value500'); $Desc->AddInteger('Value1000'); $Desc->AddInteger('Value2000'); $Desc->AddInteger('Value5000'); return $Desc; } } class FinanceBankAccount extends Model { static function GetModelDesc(): ModelDesc { $Desc = new ModelDesc(self::GetClassName()); $Desc->AddReference('Subject', Subject::GetClassName()); $Desc->AddString('Comment'); $Desc->AddString('Number'); $Desc->AddReference('Bank', FinanceBank::GetClassName()); $Desc->AddDate('TimeCreate'); $Desc->AddDate('TimeEnd'); $Desc->AddReference('Currency', Currency::GetClassName()); $Desc->AddString('LoginName'); $Desc->AddString('LoginPassword'); $Desc->AddBoolean('Use'); $Desc->AddDate('LastImportDate'); $Desc->AddString('LastImportId'); $Desc->AddBoolean('AutoImport'); return $Desc; } } class FinanceBank extends Model { static function GetModelDesc(): ModelDesc { $Desc = new ModelDesc(self::GetClassName()); $Desc->AddString('Name'); $Desc->AddString('Code'); $Desc->AddString('BIC'); $Desc->AddReference('Country', Country::GetClassName()); return $Desc; } } class Currency extends Model { static function GetModelDesc(): ModelDesc { $Desc = new ModelDesc(self::GetClassName()); $Column = $Desc->AddString('Code'); $Column->MaxLength = 3; $Desc->AddString('Name'); $Desc->AddString('Symbol'); return $Desc; } } class FinanceCharge extends Model { static function GetModelDesc(): ModelDesc { $Desc = new ModelDesc(self::GetClassName()); $Desc->AddInteger('Internet'); $Desc->AddInteger('InternetSpeed'); $Desc->AddInteger('InternetSpeedReserve'); $Desc->AddInteger('AdministrationPerUser'); $Desc->AddInteger('kWh'); $Desc->AddInteger('BaseSpeedElement'); $Desc->AddInteger('BaseTariffPrice'); $Desc->AddInteger('TopTariffPrice'); $Desc->AddChangeAction(); return $Desc; } } class FinanceVat extends Model { static function GetModelDesc(): ModelDesc { $Desc = new ModelDesc(self::GetClassName()); $Desc->AddReference('Type', FinanceVatType::GetClassName()); $Desc->AddDate('ValidFrom'); $Desc->AddDate('ValidTo'); $Desc->AddInteger('Value'); return $Desc; } } class FinanceVatType extends Model { static function GetModelDesc(): ModelDesc { $Desc = new ModelDesc(self::GetClassName()); $Desc->AddString('Name'); return $Desc; } } class FinanceBillingPeriod extends Model { static function GetModelDesc(): ModelDesc { $Desc = new ModelDesc(self::GetClassName()); $Desc->AddString('Name'); $Desc->AddInteger('MonthCount'); return $Desc; } } class FinanceInvoiceOperationRel extends Model { static function GetModelDesc(): ModelDesc { $Desc = new ModelDesc(self::GetClassName()); $Desc->AddReference('Invoice', FinanceInvoice::GetClassName()); $Desc->AddReference('Operation', FinanceOperation::GetClassName()); return $Desc; } }