Changeset 507

Timestamp:

Apr 1, 2013, 7:14:59 PM (11 years ago)

Author:

chronos

Message:

• Opraveno: Generování nastavení sítě. Generování front Queue nyní pracuje s přiřazením služeb k zařízením a podsítím.
• Opraveno: Zjišťování adresáře souborů na disku dle id adresáře.

Location:

trunk

Files:

• Common/Form/Types/File.php (modified) (2 diffs)
• Common/Global.php (modified) (1 diff)
• Common/Update.php (modified) (1 diff)
• Common/Version.php (modified) (1 diff)
• Modules/File/File.php (modified) (3 diffs)
• Modules/OpeningHours/OpeningHours.php (modified) (1 diff)
• Readme.txt (modified) (3 diffs)
• ToDo.txt (modified) (1 diff)
• admin/DefaultConfig.php (modified) (2 diffs)
• admin/Updates.php (modified) (2 diffs)
• finance/finance.php (modified) (5 diffs)
• finance/manage.php (modified) (4 diffs)
• finance/tarify.php (modified) (1 diff)
• finance/user_state.php (modified) (2 diffs)
• finance/zivnost.php (modified) (1 diff)
• form_classes.php (modified) (4 diffs)
• sql/updates/479.sql (modified) (1 diff)
• system/generators/dhcp.php (modified) (2 diffs)
• system/generators/firewall_filter.php (modified) (1 diff)
• system/generators/firewall_mangle.php (modified) (2 diffs)
• system/generators/firewall_nat.php (modified) (2 diffs)
• system/generators/generate.php (modified) (1 diff)
• system/generators/netwatch.php (modified) (2 diffs)
• system/generators/netwatch_import.php (modified) (1 diff)
• system/generators/queue.php (modified) (3 diffs)

trunk/Common/Form/Types/File.php

@@ -68 +68 @@
   {
     parent::__construct($FormManager);
-    $this->FileDownloadURL = 'File.php';
+    $this->FileDownloadURL = 'file';
     $this->DirectoryId = null;
   }
@@ -75 +75 @@
   {
     $File = &$Item['Value'];
-    return('<a href="'.$this->FileDownloadURL.'?Id='.$File->Id.'">'.
+    return('<a href="'.$this->FileDownloadURL.'?id='.$File->Id.'">'.
       $File.'</a> ('.HumanSize($File->Size).')');
   }

trunk/Common/Global.php

@@ -196 +196 @@
   $System->AddModule(new Bill());
   $System->AddModule(new Finance());
+  $System->Modules['Finance']->MainSubject = $Config['Finance']['MainSubjectId'];
+  $System->Modules['Finance']->DirectoryId = $Config['Finance']['DirectoryId'];
   $System->Modules['Finance']->LoadMonthParameters(0);
   RegisterFormClasses($System->FormManager);

trunk/Common/Update.php

@@ -76 +76 @@
           echo($Query.';<br/>');
           flush();
-          $this->Database->query($Query);
+          return($this->Database->query($Query));
   }
 }

trunk/Common/Version.php

@@ -1 +1 @@
 <?php
 
-$Revision = 505; // Subversion revision
-$DatabaseRevision = 505; 
-$ReleaseTime = '2013-03-31';
+$Revision = 507; // Subversion revision
+$DatabaseRevision = 507; 
+$ReleaseTime = '2013-04-01';
 
 ?>

trunk/Modules/File/File.php

@@ -54 +54 @@
     {
       $DbRow = $DbResult->fetch_assoc();
-      if(file_exists($this->FilesDir.'/'.$DbRow['Name']))
+      if($DbRow['Directory'] != '') $FileName = $this->GetDir($DbRow['Directory']);
+        else $FileName = $this->FilesDir;
+      $FileName .= $DbRow['Name'];
+      if(file_exists($FileName))
       {
-        Header('Content-Type: '.$this->DetectMimeType($this->FilesDir.'/'.$DbRow['Name']));
+        Header('Content-Type: '.$this->DetectMimeType($FileName));
         Header('Content-Disposition: attachment; filename="'.$DbRow['Name'].'"');
-        echo(file_get_contents($this->FilesDir.'/'.$DbRow['Name']));
+        echo(file_get_contents($FileName));
       } else echo('Soubor nenalezen!');
     } else echo('Soubor nenalezen!');
   }
+  
+  function GetDir($Id)
+  {
+    $DbResult = $this->Database->select('FileDirectory', '*', 'Id='.$Id);
+    $DbRow = $DbResult->fetch_assoc();
+    if($DbRow['Parent'] != '') $Result = $this->GetDir($DbRow['Parent']);
+      else $Result = $this->FilesDir;
+    $Result .= $DbRow['Name'].'/';
+    return($Result);
+  }
 }
+
 
 class PageFile extends Page
@@ -73 +87 @@
   }
 }
-
 
 class ModuleFile extends AppModule
@@ -105 +118 @@
     $this->System->RegisterPage('file', 'PageFile');
     $File = new File($this->System->Database);
-    $File->FilesDir = dirname(__FILE__).'/../../'.$Config['Web']['UploadFileFolder'].'/';    
+    $File->FilesDir = dirname(__FILE__).'/../../'.$Config['Web']['UploadFileFolder'];    
     $this->System->AddModule($File);
   }  

trunk/Modules/OpeningHours/OpeningHours.php

@@ -187 +187 @@
       if($Subject['Notice'] != '') $Output .= 'Poznámka: '.$Subject['Notice'].'<br />';
       
-      if($Subject['Photo'] != 0) $Output .= '<a href="FileDownload.php?Id='.$Subject['Photo'].'">Fotka</a> ';
+      if($Subject['Photo'] != 0) $Output .= '<a href="file?id='.$Subject['Photo'].'">Fotka</a> ';
       
       if($this->System->Modules['User']->CheckPermission('SubjectOpenTime', 'Edit'))

trunk/Readme.txt

@@ -2 +2 @@
 ========================================
 
-1) Konfigurační soubory
+1) Instalace a nastavení systému
 
-  Zkopírovat soubor config.sample.php na config.php a vyplnit údaje.
+  V prohlížeči otevřete podadresář admin v umístění, ze kterého je web dostupný.
+  Zde stiskem tlačítka nastavení nastavíte údaje pro připojení do databáze.
+  Následně stiskem tlačítka Instalovat se nainstalují tabulky do databáze.
 
 
@@ -17 +19 @@
 
   finance/doklady - účetní doklady
-  aktuality/uploads - přílohy aktualit
+  files - úložiště souborů
   galerie - fotogalerie obrázků
   files - načtené soubory přes web
@@ -26 +28 @@
   files - vlastníka nastavit na apache
   finance/doklady - vlastníka nastavit na apache
-  aktuality/uploads - vlastníka nastavit na apache
 
   

trunk/ToDo.txt

@@ -28 +28 @@
 - Automatické zasílání SMS informací
 - Vytvořit modul pro záznam a vykreslování časových grafů
-
+- V modulu News předělat přílohy na odkazy do tabulky File
 
 Hotové

trunk/admin/DefaultConfig.php

@@ -26 +26 @@
   array('Name' => 'Web/UserSupport', 'Type' => 'Boolean', 'Default' => '1', 'Title' => 'Podpora uživatelů'),
   array('Name' => 'Web/UploadFileFolder', 'Type' => 'String', 'Default' => 'files', 'Title' => 'Složka načtených souborů'),
-  array('Name' => 'Web/FileRootPath', 'Type' => 'String', 'Default' => 'finance/doklady', 'Title' => 'Složka generovaných dokladů'),
   array('Name' => 'Web/News/DaysAgo', 'Type' => 'Integer', 'Default' => '30', 'Title' => 'Stáří zobrazovaných aktualit'),
   array('Name' => 'Web/News/Count', 'Type' => 'Integer', 'Default' => '5', 'Title' => 'Počet zobrazovaných aktualit na skupinu'),
+  array('Name' => 'Web/News/DirectoryId', 'Type' => 'Integer', 'Default' => '1', 'Title' => 'Id adresáře souborů příloh aktualit'),
   array('Name' => 'Web/GoogleMapsApiKey', 'Type' => 'String', 'Default' => '', 'Title' => 'API google map'),
   array('Name' => 'Web/ItemsPerPage', 'Type' => 'Integer', 'Default' => '50', 'Title' => 'Položek na stránku'),
@@ -39 +39 @@
   array('Name' => 'Finance/MainUserId', 'Type' => 'Integer', 'Default' => '1', 'Title' => 'Id hlavního uživatele'),
   array('Name' => 'Finance/MainSubjectId', 'Type' => 'Integer', 'Default' => '1', 'Title' => 'Id hlavního subjektu'),
+  array('Name' => 'Finance/DirectoryId', 'Type' => 'Integer', 'Default' => '1', 'Title' => 'Id adresáře souborů dokladů'),
   array('Name' => 'MainRouter/HostName', 'Type' => 'String', 'Default' => 'localhost', 'Title' => 'Hlavní brána'),
   array('Name' => 'MainRouter/UserName', 'Type' => 'String', 'Default' => 'admin', 'Title' => 'Uživatelské jméno'),

trunk/admin/Updates.php

@@ -183 +183 @@
   `Message` varchar(255) NOT NULL DEFAULT '',
   PRIMARY KEY (`Id`)
-) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;");
-  
+) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;"); 
+}
+
+function UpdateTo507($Manager)
+{
+  $Manager->Execute("INSERT INTO `ISMenuItem` (`Id` ,`Name` ,`Parent` ,`Table` ,`IconName`) ".
+    "VALUES (NULL , 'Aktuality', NULL, '', '');");
+  $Id = $Manager->Database->insert_id;
+  $Manager->Execute("INSERT INTO `ISMenuItem` (`Id` ,`Name` ,`Parent` ,`Table` ,`IconName`) ".
+    "VALUES (NULL , 'Aktuality', '".$Id."', 'News', '');");
+  $Manager->Execute("INSERT INTO `ISMenuItem` (`Id` ,`Name` ,`Parent` ,`Table` ,`IconName`) ".
+    "VALUES (NULL , 'Kategorie aktualit', '".$Id."', 'NewsCategory', '');");
+  $Manager->Execute("INSERT INTO `FileDirectory` (`Id` ,`Name` ,`Parent`".
+    ") VALUES (NULL , 'news', NULL);");
+  $Manager->Execute("ALTER TABLE `File` ADD `Time` DATETIME NOT NULL ");
+  // Convert Hire column to Service item
+  $Manager->Execute("INSERT INTO `ServiceCategory` (
+`Id` ,`Name`) VALUES (NULL , 'Pevný nájem');");
+  $ServiceCategoryHire = $Manager->Database->insert_id;  
+  $DbResult = $Manager->Execute("SELECT * FROM Member WHERE Hire>0");
+  while($Member = $DbResult->fetch_assoc())
+  {
+    $Manager->Execute("INSERT INTO `Service` (
+`Id` ,`Name` ,`Category` ,`Price` ,`VAT` ,`CustomerCount` ,`Action` ,`ReplaceId` ,
+`Public` ,`InternetSpeedMin` ,`InternetSpeedMax` ,`UploadAsymmetry` ,`Memory` ,`MemorySwap` ,`Storage` ,`CPUCount`)
+VALUES (
+NULL , 'Nájem', '".$ServiceCategoryHire."', '-".$Member['Hire']."', '2', '', NULL , NULL , '', '', '', '', '', '', '', ''
+);");
+    $ServiceHire = $Manager->Database->insert_id;  
+    $Manager->Execute("INSERT INTO `ServiceCustomerRel` (`Service`, `Customer`) VALUES ". 
+      "(".$ServiceHire.",".$Member['Id'].")");
+  }
+  $Manager->Execute("ALTER TABLE `Member` DROP `Hire`");
+  // Each device and subnet have to be assigned to payed service to determine service parameters
+  $Manager->Execute("ALTER TABLE `NetworkDevice` ADD `Service` INT NULL AFTER `Location` ,
+ADD INDEX ( `Service` )");
+  $Manager->Execute("ALTER TABLE `NetworkDevice` ADD FOREIGN KEY ( `Service` ) REFERENCES `ServiceCustomerRel` (
+`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;");
+  $Manager->Execute("UPDATE `NetworkDevice` SET `Service` = (SELECT `Id` FROM `ServiceCustomerRel` WHERE `ServiceCustomerRel`.`Customer` = `NetworkDevice`.`Member` LIMIT 1)");
+  $Manager->Execute("ALTER TABLE `NetworkSubnet` ADD `Service` INT NULL AFTER `Member` ,
+ADD INDEX ( `Service` ) ");
+  $Manager->Execute("ALTER TABLE `NetworkSubnet` ADD FOREIGN KEY ( `Service` ) REFERENCES `ServiceCustomerRel` (
+`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;");
+  $Manager->Execute("UPDATE `NetworkSubnet` SET `Service` = (SELECT `Id` FROM `ServiceCustomerRel` WHERE `ServiceCustomerRel`.`Customer` = `NetworkSubnet`.`Member` LIMIT 1)"); 
 }
 
@@ -197 +239 @@
   500 => array('Revision' => 502, 'Function' => 'UpdateTo502'),
   502 => array('Revision' => 505, 'Function' => 'UpdateTo505'),
+  505 => array('Revision' => 507, 'Function' => 'UpdateTo507'),
 );
 

trunk/finance/finance.php

@@ -26 +26 @@
   var $Tariffs;
   var $ExternalSubject = 96;
-  var $MainSubject = 71;
+  var $MainSubject;
   var $BillingPeriods;
+  var $DirectoryId;
   
   function LoadTariffs()
@@ -35 +36 @@
     while($Tariff = $DbResult->fetch_array())
     {
-      $Tariff['InternetSpeedMin'] = $Tariff['InternetSpeedMin'] * 1024;
-      $Tariff['InternetSpeedMax'] = $Tariff['InternetSpeedMax'] * 1024;
+      $Tariff['InternetSpeedMin'] = $Tariff['InternetSpeedMin'] * 1000;
+      $Tariff['InternetSpeedMax'] = $Tariff['InternetSpeedMax'] * 1000;
       $this->Tariffs[$Tariff['Id']] = $Tariff;
     }  
@@ -43 +44 @@
   function RecalculateTariffs()
   {
-    $ResidualSpeed = $this->MaxSpeed * 1024;
+    $ResidualSpeed = $this->MaxSpeed * 1000;
   
     $this->LoadTariffs();
@@ -92 +93 @@
       }
       $this->Database->update('Service', 'Id='.$Tariff['Id'], 
-        array('InternetSpeedMin' => ($Tariff['InternetSpeedMin'] / 1024), 
+        array('InternetSpeedMin' => ($Tariff['InternetSpeedMin'] / 1000), 
         'CustomerCount' => $Tariff['CustomerCount']));
     }
@@ -211 +212 @@
         else $MonthlyInet = 0;
       
-      //if($Row['inet'] == 1)
       $Monthly += $MonthlyInet; 
       $Monthly -= $this->W2Kc($ConsumptionPlus); 
-      $Monthly -= $Member['Hire'];
-      //if($Row['overheads'] == 1) $Monthly += $Sprava; // + W2Kc($Consumption); 
       $Monthly = round($Monthly);
       

trunk/finance/manage.php

@@ -381 +381 @@
       }
       $Period = $this->GetBillingPeriod($Member['BillingPeriod']);
-      if(($Period['From'] > $Member['UNIX_TIMESTAMP(Member.BillingPeriodLastDate)']) and
-          ($Member['Blocked'] == 0))
+      if(($Period['MonthCount'] > 0) and ($Member['Blocked'] == 0) and
+        ($Period['From'] > $Member['UNIX_TIMESTAMP(Member.BillingPeriodLastDate)']))
       {
         $InvoiceItems = array();
@@ -398 +398 @@
           $MonthlyTotal += $Service['Price'];
         }
-        if($Member['Hire'] != 0)
-        {
-          $Output .= '<tr><td>Nájem</td><td>'.(-$Member['Hire']).'</td></tr>';
-          $MonthlyTotal -= $Member['Hire'];
-        }
         if($Member['MonthlyPlus'] != 0)
         {
+          $InvoiceItems[] = array('Description' => 'Spotřeba energie', 'Price' => -$Member['MonthlyPlus'],
+            'Quantity' => $Period['MonthCount'], 'VAT' => 2);
           $MonthlyTotal -= $Member['MonthlyPlus'];
         }
@@ -610 +607 @@
     while($Row = $DbResult->fetch_assoc())
     {
-      $DbResult2 = $this->Database->insert('File', array('Name' => '', 'Size' => 0));
+      $DbResult2 = $this->Database->insert('File', array('Name' => '', 'Size' => 0,
+        'Directory' => $this->System->Modules['Finance']->DirectoryId, 'Time' => 'NOW()'));
       $FileId = $this->Database->insert_id;
       $FileName = 'doklad-'.$FileId.'.pdf';
@@ -617 +615 @@
       $Bill->System = &$this->System;
       $Bill->InvoiceId = $Row['Id'];
-      $FullFileName = $this->System->Modules['File']->FilesDir.$FileName;
+      $FullFileName = $this->System->Modules['File']->GetDir($this->System->Modules['Finance']->DirectoryId).$FileName;
       $Bill->SaveToFile($FullFileName);
-      $this->Database->update('File', 'Id='.$FileId, array('Name' => $FileName, 'Size' => filesize($FullFileName)));
-      $this->Database->update('FinanceInvoice', 'Id='.$Row['Id'], array('File' => $FileId));
-      $Output .= '.';
+      if(file_exists($FullFileName))
+      {
+        $this->Database->update('File', 'Id='.$FileId, array('Name' => $FileName, 'Size' => filesize($FullFileName)));
+        $this->Database->update('FinanceInvoice', 'Id='.$Row['Id'], array('File' => $FileId));
+        $Output .= '.';
+      } else $Output .= 'Soubor "'.$FullFileName.'" se nepodařilo uložit.';
     }
 

trunk/finance/tarify.php

@@ -25 +25 @@
     {
       $Tariff['Aggregation'] = '1:'.round($Tariff['InternetSpeedMax'] / $Tariff['InternetSpeedMin']);
-      $Output .= '<tr><td style="color: blue;">'.$Tariff['Name'].'</td><td align="center">'.round($Tariff['InternetSpeedMin'] / 1024).'</td><td align="center" style="color: blue;">'.round($Tariff['InternetSpeedMax'] / 1024).'</td><td align="center">'.$Tariff['Aggregation'].'</td><td align="center">'.$Tariff['CustomerCount'].'</td><td align="center" style="color: blue;">'.$Tariff['Price'].'</td><td align="center">'.$Tariff['CustomerCount'] * $Tariff['Price'].'</td></tr>';
+      $Output .= '<tr><td style="color: blue;">'.$Tariff['Name'].'</td><td align="center">'.round($Tariff['InternetSpeedMin'] / 1000).'</td><td align="center" style="color: blue;">'.round($Tariff['InternetSpeedMax'] / 1000).'</td><td align="center">'.$Tariff['Aggregation'].'</td><td align="center">'.$Tariff['CustomerCount'].'</td><td align="center" style="color: blue;">'.$Tariff['Price'].'</td><td align="center">'.$Tariff['CustomerCount'] * $Tariff['Price'].'</td></tr>';
       $TotalPrice = $TotalPrice + $Tariff['Price'] * $Tariff['CustomerCount'];
       $TotalMemberCount = $TotalMemberCount + $Tariff['CustomerCount'];

trunk/finance/user_state.php

@@ -85 +85 @@
       if($Row['Value'] > 0) $Row['Value'] = '+'.$Row['Value'];
       if($Row['BillCode'] == '') $Row['BillCode'] = 'PDF';
-      if($Row['File'] > 0) $Invoice = '<a href="'.$this->System->Link('/FileDownload.php?id='.$Row['File']).'">'.$Row['BillCode'].'</a>'; 
+      if($Row['File'] > 0) $Invoice = '<a href="'.$this->System->Link('/file?id='.$Row['File']).'">'.$Row['BillCode'].'</a>'; 
         else $Invoice = '&nbsp;';
       if($Row['PeriodFrom'] != '') $Period = HumanDate($Row['PeriodFrom']).' - '.HumanDate($Row['PeriodTo']);
@@ -118 +118 @@
     }
     
-    if($Member['Hire'] != 0)
-    {
-      $Output .= '<tr><td>Nájem</td><td>'.(-$Member['Hire']).'</td></tr>';
-      $Total -= $Member['Hire'];
-    }
     if($Member['MonthlyPlus'] != 0)
     {

trunk/finance/zivnost.php

@@ -8 +8 @@
   var $ShortTitle = 'Daňová evidence';
   var $ExternalSubject = 96;
-  var $MainSubject = 71;
   var $StartEvidence = 0;
 

trunk/form_classes.php

@@ -150 +150 @@
     ),
     'ItemActions' => array(
-      array('Caption' => 'Stáhnout', 'URL' => '/FileDownload.php?download'),
+      array('Caption' => 'Stáhnout', 'URL' => '/file?download'),
     ),
   ),
@@ -456 +456 @@
       'BillingPeriodLastDate' => array('Type' => 'Date', 'Caption' => 'Datum poslední fakturace', 'Default' => ''),
       'NetworkSegment' => array('Type' => 'TNetworkSegment', 'Caption' => 'Úsek sítě', 'Default' => '', 'Null' => true),
-      'Hire' => array('Type' => 'Integer', 'Caption' => 'Nájem', 'Default' => '0', 'Suffix' => 'Kč'),
       'Blocked' => array('Type' => 'Boolean', 'Caption' => 'Blokování', 'Default' => '0'),
       'PayDay' => array('Type' => 'Integer', 'Caption' => 'Den placení', 'Default' => '0', 'Suffix' => 'den'),
@@ -557 +556 @@
       'Member' => array('Type' => 'TMember', 'Caption' => 'Zákazník', 'Default' => '0'),
       'Location' => array('Type' => 'TMember', 'Caption' => 'Umístění', 'Default' => '0'),
+      'Service' => array('Type' => 'TServiceCustomerRel', 'Caption' => 'Služba', 'Default' => '', 'Null' => true),
       'Used' => array('Type' => 'Boolean', 'Caption' => 'Použito', 'Default' => '1'),
       'Online' => array('Type' => 'Boolean', 'Caption' => 'Běží', 'Default' => '0', 'ReadOnly' => true),
@@ -810 +810 @@
   'News' => array(
     'Title' => 'Nová aktualita',
-    'Table' => '',
-    'SubmitText' => 'Vložit',
+    'Table' => 'News',
     'Items' => array(
       'Category' => array('Type' => 'TNewsCategory', 'Caption' => 'Kategorie', 'Default' => 0),
       'Title' => array('Type' => 'String', 'Caption' => 'Nadpis', 'Default' => ''),
       'Content' => array('Type' => 'Text', 'Caption' => 'Obsah', 'Default' => ''),
-      'Enclosure1' => array('Type' => 'File', 'Caption' => 'Přílohy (Max. velikost souboru 1 MB)', 'Default' => ''),
-      'Enclosure2' => array('Type' => 'File', 'Caption' => '', 'Default' => ''),
-      'Enclosure3' => array('Type' => 'File', 'Caption' => '', 'Default' => ''),
+      'Date' => array('Type' => 'Date', 'Caption' => 'Datum', 'Default' => ''),
+      'Author' => array('Type' => 'String', 'Caption' => 'Autor', 'Default' => ''),
+      'Enclosure' => array('Type' => 'String', 'Caption' => 'Přílohy', 'Default' => ''),
+      'User' => array('Type' => 'TUser', 'Caption' => 'Uživatel', 'Default' => ''),
+      'IP' => array('Type' => 'String', 'Caption' => 'IP adresa', 'Default' => '', 'ReadOnly' => true),
+      'Link' => array('Type' => 'Hyperlink', 'Caption' => 'Odkaz', 'Default' => ''),
+    ),
+  ),
+  'NewsCategory' => array(
+    'Title' => 'Kategorie aktualit',
+    'Table' => 'NewsCategory',
+    'Items' => array(
+      'Caption' => array('Type' => 'String', 'Caption' => 'Titulek', 'Default' => ''),
+      'RSS' => array('Type' => 'Hyperlink', 'Caption' => 'Zdroj RSS', 'Default' => ''),
+      'Permission' => array('Type' => 'Boolean', 'Caption' => 'Veřejné upravitelné', 'Default' => ''),
+      'Sequence' => array('Type' => 'Integer', 'Caption' => 'Pořadí', 'Default' => ''),
+      'Group' => array('Type' => 'Integer', 'Caption' => 'Skupina', 'Default' => ''),
     ),
   ),

trunk/sql/updates/479.sql

@@ -46 +46 @@
 -- Transform data from FinanceTariff
 INSERT INTO Service (SELECT Id AS Id, Name, `Group` AS Category, Price, MemberCount AS CustomerCount, 
-ReplaceId, Public, SpeedMax AS InternetSpeedMax, SpeedMin AS InternetSpeedMin, 
+ReplaceId, Public, SpeedMin AS InternetSpeedMin, SpeedMax AS InternetSpeedMax,
 UploadAsymmetry, 0 AS Memory, 0 AS MemorySwap, 0 AS Storage, 0 AS CPUCount FROM FinanceTariff);
 
-INSERT INTO ServiceCustomerRel (SELECT NULL AS Id, InternetTariffCurrentMonth AS Serice, Id AS Customer, 0 AS Period FROM `Member`);
-INSERT INTO ServiceCustomerRel (SELECT NULL AS Id, InternetTariffNextMonth AS Serice, Id AS Customer, 1 AS Period FROM `Member`);
+INSERT INTO ServiceCustomerRel (SELECT NULL AS Id, InternetTariffCurrentMonth AS Service, Id AS Customer, 0 AS Period FROM `Member`);
+INSERT INTO ServiceCustomerRel (SELECT NULL AS Id, InternetTariffNextMonth AS Service, Id AS Customer, 1 AS Period FROM `Member`);
 
 ALTER TABLE `Member` DROP FOREIGN KEY `Member_ibfk_25` ;

trunk/system/generators/dhcp.php

@@ -5 +5 @@
 include('../routerboard.php');
 $Path = array('ip', 'dhcp-server', 'lease');
+$Database = &$System->Database;
 
 $Routerboard = new Routerboard();
@@ -20 +21 @@
   $DbResult2 = $Database->query('SELECT NetworkInterface.*, NetworkDevice.Name AS DeviceName FROM `NetworkInterface` LEFT JOIN NetworkDevice ON NetworkDevice.Id = NetworkInterface.Device WHERE '.
   ' CompareNetworkPrefix(INET_ATON(LocalIP), INET_ATON("'.$Subnet['AddressRange'].'"), '.$Subnet['Mask'].') AND (`MAC` != "00:00:00:00:00:00") ORDER BY `LocalIP`');
-  //echo($Database->error);
   while($Interface = $DbResult2->fetch_assoc())
   {

trunk/system/generators/firewall_filter.php

@@ -6 +6 @@
 include('common.php');
 $Path = array('ip', 'firewall', 'filter');
+$Database = &$System->Database;
 
 $Routerboard = new Routerboard($Config['MainRouter']['HostName']);

trunk/system/generators/firewall_mangle.php

@@ -9 +9 @@
 include('common.php');
 $PathFirewall = array('ip', 'firewall', 'mangle');
+$Database = &$System->Database;
 
 $Routerboard = new Routerboard();
@@ -33 +34 @@
 
 // Process users
-$DbResult = $Database->query('SELECT `Member`.*, `Subject`.`Name` FROM `Member` LEFT JOIN `Subject` ON `Subject`.`Id` = `Member`.`Subject`');
+$DbResult = $Database->query('SELECT `Member`.*, `Subject`.`Name` FROM `Member` '.
+  'LEFT JOIN `Subject` ON `Subject`.`Id` = `Member`.`Subject` '.
+  'WHERE `Member`.`Blocked` = 0');
 while($Member = $DbResult->fetch_assoc())
 {

trunk/system/generators/firewall_nat.php

@@ -6 +6 @@
 include('common.php');
 $Path = array('ip', 'firewall', 'nat');
+$Database = &$System->Database;
 
 $Routerboard = new Routerboard($Config['MainRouter']['HostName']);
@@ -36 +37 @@
 //$Items[] = array('chain' => 'inet-in', 'dst-address' => '172.16.1.1/30', 'action' => 'accept', 'comment' => 'Local_subnet');
 
-$DbResult = $Database->query('SELECT `Member`.*, `Subject`.`Name` FROM `Member` JOIN `Subject` ON `Member`.`Subject` = `Subject`.`Id`');
+$DbResult = $Database->query('SELECT `Member`.*, `Subject`.`Name` FROM `Member` '.
+  'LEFT JOIN `Subject` ON `Subject`.`Id` = `Member`.`Subject` '.
+  'WHERE `Member`.`Blocked` = 0');
 while($Member = $DbResult->fetch_assoc())
 {

trunk/system/generators/generate.php

@@ -3 +3 @@
 if(isset($_SERVER['REMOTE_ADDR'])) die();
 include('../../Common/Global.php');
+$Database = &$System->Database;
 
 $Now = time();

trunk/system/generators/netwatch.php

@@ -5 +5 @@
 include('../routerboard.php');
 $Path = array('tool', 'netwatch');
+$Database = &$System->Database;
 
 $Routerboard = new Routerboard();
@@ -20 +21 @@
   while($Subnet = $DbResult->fetch_assoc())
   {
-    //print_r($Subnet);
     $I = explode('.', $Subnet['AddressRange']);
     $I = $I[2];

trunk/system/generators/netwatch_import.php

@@ -7 +7 @@
 $Path = array('tool', 'netwatch');
 $Period = 60; // every 60 seconds
+$Database = &$System->Database;
 
 while(1)

trunk/system/generators/queue.php

@@ -9 +9 @@
 include('common.php');
 $PathQueue = array('queue', 'tree');
+$Database = &$System->Database;
 
 $Routerboard = new Routerboard();
@@ -27 +28 @@
 $InDivider = 1;
 $OutDivider = 1;
-$TotalMaxSpeedIn = round($Finance->RealMaxSpeed / $InDivider) * 1024;
-$TotalMaxSpeedOut = round($Finance->RealMaxSpeed / $OutDivider) * 1024;
-$UsersMaxSpeedIn = round($Finance->MaxSpeed / $InDivider) * 1024;
-$UsersMaxSpeedOut = round($Finance->MaxSpeed / $OutDivider) * 1024;
+$TotalMaxSpeedIn = round($Finance->RealMaxSpeed / $InDivider) * 1000;
+$TotalMaxSpeedOut = round($Finance->RealMaxSpeed / $OutDivider) * 1000;
+$UsersMaxSpeedIn = round($Finance->MaxSpeed / $InDivider) * 1000;
+$UsersMaxSpeedOut = round($Finance->MaxSpeed / $OutDivider) * 1000;
 $VoipMaxSpeedIn = $TotalMaxSpeedIn - 136;
 $VoipMaxSpeedOut = $TotalMaxSpeedOut - 136;
@@ -55 +56 @@
 
 // Process users
-$DbResult = $Database->query('SELECT `Member`.*, `Subject`.`Name` FROM `Member` LEFT JOIN `Subject` ON `Subject`.`Id` = `Member`.`Subject`');
+$DbResult = $Database->query('SELECT `Member`.*, `Subject`.`Name` FROM `Member` LEFT JOIN `Subject` ON `Subject`.`Id` = `Member`.`Subject` WHERE `Member`.`Blocked`=0');
 while($Member = $DbResult->fetch_assoc())
 {
   $Member['Name'] = RouterOSIdent($Member['Name'].'-'.$Member['Id'] );
 
-  echo('Uživatel '.$Member['Name'].': ');
-  $Tariff = $Finance->Tariffs[$Member['InternetTariffCurrentMonth']]; 
-  $SpeedIn = round($Tariff['InternetSpeedMin'] / $InDivider);
-  $SpeedOut = round($Tariff['InternetSpeedMin'] / $OutDivider);
-  $UserMaxSpeedIn = round($Tariff['InternetSpeedMax'] / $InDivider);
-  $UserMaxSpeedOut = round($Tariff['InternetSpeedMax'] / $OutDivider);
-  $ItemsQueue[] = array('name' => $Member['Name'].'-out', 'limit-at' => $SpeedIn, 'max-limit' => $UserMaxSpeedIn, 'parent' => 'main-out');
-  $ItemsQueue[] = array('name' => $Member['Name'].'-in', 'limit-at' => $SpeedOut, 'max-limit' => $UserMaxSpeedOut, 'parent' => 'main-in');
+  echo('Zákazník '.$Member['Name']."\n");
+  $DbResult4 = $Database->query('SELECT `Service`.*, `ServiceCustomerRel`.`Id` AS `RelId` FROM `ServiceCustomerRel` JOIN `Service` ON `Service`.`Id` = `ServiceCustomerRel`.`Service` '.
+    'WHERE (`ServiceCustomerRel`.`Customer` = '.$Member['Id'].') AND (`ServiceCustomerRel`.`Action` IS NULL)');
+  while($Service = $DbResult4->fetch_assoc())
+  {
+    echo('Služba '.$Service['Name'].': ');
+    $SpeedIn = round($Service['InternetSpeedMin'] / $InDivider) * 1000;
+    $SpeedOut = round($Service['InternetSpeedMin'] / $OutDivider) * 1000;
+    $UserMaxSpeedIn = round($Service['InternetSpeedMax'] / $InDivider) * 1000;
+    $UserMaxSpeedOut = round($Service['InternetSpeedMax'] / $OutDivider) * 1000;
+    $ItemsQueue[] = array('name' => $Member['Name'].'-out', 'limit-at' => $SpeedIn, 'max-limit' => $UserMaxSpeedIn, 'parent' => 'main-out');
+    $ItemsQueue[] = array('name' => $Member['Name'].'-in', 'limit-at' => $SpeedOut, 'max-limit' => $UserMaxSpeedOut, 'parent' => 'main-in');
 
-  $DbResult2 = $Database->select('NetworkDevice', 'COUNT(*)', '(`Used` = 1) AND (`Member`='.$Member['Id'].')');
-  $Row = $DbResult2->fetch_row();
-  $HostCount = $Row[0];
-  if($HostCount > 0)
-  {
-    $HostSpeedIn = round($SpeedIn / $HostCount);
-    $HostSpeedOut = round($SpeedOut / $HostCount);
-  } else
-  {
-    $HostSpeedIn = $SpeedIn;
-    $HostSpeedOut = $SpeedOut;
+    $Filter = '(`Used` = 1) AND (`Service` = '.$Service['RelId'].')';
+    $DbResult2 = $Database->select('NetworkDevice', 'COUNT(*)', $Filter);
+    $Row = $DbResult2->fetch_row();
+    $HostCount = $Row[0];
+    if($HostCount > 0)
+    {
+      $HostSpeedIn = round($SpeedIn / $HostCount);
+      $HostSpeedOut = round($SpeedOut / $HostCount);
+    } else
+    {
+      $HostSpeedIn = $SpeedIn;
+      $HostSpeedOut = $SpeedOut;
+    }
+
+    $DbResult2 = $Database->select('NetworkDevice', '*', $Filter);
+    while($Device = $DbResult2->fetch_assoc())
+    {
+      $DbResult3 = $Database->select('NetworkInterface', '*', '`Device` = '.$Device['Id'].' AND `LocalIP` != ""');
+      while($Interface = $DbResult3->fetch_assoc())
+      {
+        $Name = $Device['Name'];
+        if($Interface['Name'] != '') $Name .= '-'.$Interface['Name'];
+        $Name = RouterOSIdent($Name);
+        echo($Name.', ');
+        $IPParts = explode('.', $Interface['LocalIP']);
+        $Subnet = $IPParts[2];
+        $PacketMark = GetMarkByComment($Name.'-out');
+        $ItemsQueue[] = array('name' => $Name.'-out', 'limit-at' => $HostSpeedIn, 'max-limit' => $UserMaxSpeedIn, 'parent' => $Member['Name'].'-out', 'packet-mark' => $PacketMark);
+        $PacketMark = GetMarkByComment($Name.'-in');
+        $ItemsQueue[] = array('name' => $Name.'-in', 'limit-at' => $HostSpeedOut, 'max-limit' => $UserMaxSpeedOut, 'parent' => $Member['Name'].'-in', 'packet-mark' => $PacketMark);
+      }
+    }
+  
+    $DbResult2 = $Database->select('NetworkSubnet', '*', '`Service`='.$Service['RelId']);
+    while($Subnet = $DbResult2->fetch_assoc())
+    {
+      $Subnet['Name'] = RouterOSIdent('subnet-'.$Subnet['Name']);
+      echo($Subnet['Name'].', ');
+      $IPParts = explode('.', $Subnet['AddressRange']);
+      $SubnetNumber = $IPParts[2];
+      $PacketMark = GetMarkByComment($Subnet['Name'].'-out');
+      $ItemsQueue[] = array('name' => $Subnet['Name'].'-out', 'limit-at' => $HostSpeedIn, 'max-limit' => $UserMaxSpeedIn, 'parent' => $Member['Name'].'-out', 'packet-mark' => $PacketMark);
+      $PacketMark = GetMarkByComment($Subnet['Name'].'-in');
+      $ItemsQueue[] = array('name' => $Subnet['Name'].'-in', 'limit-at' => $HostSpeedOut, 'max-limit' => $UserMaxSpeedOut, 'parent' => $Member['Name'].'-in', 'packet-mark' => $PacketMark);
+    }
+    echo("\n");
   }
-
-  $DbResult2 = $Database->select('NetworkDevice', '*', '`Used` = 1 AND `Member` = '.$Member['Id']);
-  while($Device = $DbResult2->fetch_assoc())
-  {
-    $DbResult3 = $Database->select('NetworkInterface', '*', '`Device` = '.$Device['Id'].' AND `LocalIP` != ""');
-    while($Interface = $DbResult3->fetch_assoc())
-    {
-      $Name = $Device['Name'];
-      if($Interface['Name'] != '') $Name .= '-'.$Interface['Name'];
-      $Name = RouterOSIdent($Name);
-      echo($Name.', ');
-      $IPParts = explode('.', $Interface['LocalIP']);
-      $Subnet = $IPParts[2];
-      $PacketMark = GetMarkByComment($Name.'-out');
-      $ItemsQueue[] = array('name' => $Name.'-out', 'limit-at' => $HostSpeedIn, 'max-limit' => $UserMaxSpeedIn, 'parent' => $Member['Name'].'-out', 'packet-mark' => $PacketMark);
-      $PacketMark = GetMarkByComment($Name.'-in');
-      $ItemsQueue[] = array('name' => $Name.'-in', 'limit-at' => $HostSpeedOut, 'max-limit' => $UserMaxSpeedOut, 'parent' => $Member['Name'].'-in', 'packet-mark' => $PacketMark);
-    }
-  }
-  
-  $DbResult2 = $Database->select('NetworkSubnet', '*', '`Member`='.$Member['Id']);
-  while($Subnet = $DbResult2->fetch_assoc())
-  {
-    $Subnet['Name'] = RouterOSIdent('subnet-'.$Subnet['Name']);
-    echo($Subnet['Name'].', ');
-    $IPParts = explode('.', $Subnet['AddressRange']);
-    $SubnetNumber = $IPParts[2];
-    $PacketMark = GetMarkByComment($Subnet['Name'].'-out');
-    $ItemsQueue[] = array('name' => $Subnet['Name'].'-out', 'limit-at' => $HostSpeedIn, 'max-limit' => $UserMaxSpeedIn, 'parent' => $Member['Name'].'-out', 'packet-mark' => $PacketMark);
-    $PacketMark = GetMarkByComment($Subnet['Name'].'-in');
-    $ItemsQueue[] = array('name' => $Subnet['Name'].'-in', 'limit-at' => $HostSpeedOut, 'max-limit' => $UserMaxSpeedOut, 'parent' => $Member['Name'].'-in', 'packet-mark' => $PacketMark);
-  }
-  echo("\n");
 }