Changeset 317

Timestamp:

Jul 15, 2011, 8:26:28 AM (13 years ago)

Author:

george

Message:

• Uvozovky v názvech v SQL dotazech.

Location:

trunk/system

Files:

• generators/firewall_mangle.php (modified) (1 diff)
• generators/firewall_nat.php (modified) (2 diffs)
• generators/netwatch.php (modified) (1 diff)
• generators/netwatch_import.php (modified) (3 diffs)
• generators/queue.php (modified) (1 diff)
• routerboard.php (modified) (1 diff)

trunk/system/generators/firewall_mangle.php

@@ -23 +23 @@
 
 // Divide rules by subnet number
-$DbResult = $Database->query('SELECT `Id`, `Name`, `AddressRange`, `Mask` FROM `NetworkSubnet` WHERE Member=0');
+$DbResult = $Database->query('SELECT `Id`, `Name`, `AddressRange`, `Mask` FROM `NetworkSubnet` WHERE `Member` IS NULL');
 while($Subnet = $DbResult->fetch_assoc())
 {

trunk/system/generators/firewall_nat.php

@@ -36 +36 @@
 //$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` JOIN `Subject` ON `Member`.`Subject` = `Subject`.`Id`');
 while($Member = $DbResult->fetch_assoc())
 {
   echo($Member['Name'].': ');
   // Hosts
-  $DbResult2 = $Database->query('SELECT NetworkInterface.*, NetworkDevice.Name AS DeviceName, NetworkDevice.InboundNATPriority FROM NetworkInterface LEFT JOIN NetworkDevice ON NetworkDevice.Id = NetworkInterface.Device WHERE (NetworkInterface.ExternalIP <> "") AND (NetworkDevice.Member = '.$Member['Id'].') AND (NetworkInterface.LocalIP != NetworkInterface.ExternalIP) ORDER BY id DESC');
+  $DbResult2 = $Database->query('SELECT `NetworkInterface`.*, `NetworkDevice`.`Name` AS `DeviceName`, `NetworkDevice`.`InboundNATPriority` FROM `NetworkInterface`'.
+  ' LEFT JOIN `NetworkDevice` ON `NetworkDevice`.`Id` = `NetworkInterface`.`Device` WHERE (`NetworkInterface`.`ExternalIP` <> "")'.
+  ' AND (`NetworkDevice`.`Member` = '.$Member['Id'].') AND (`NetworkInterface`.`LocalIP` != `NetworkInterface`.`ExternalIP`) ORDER BY `id` DESC');
   while($Interface = $DbResult2->fetch_assoc())
   {
@@ -60 +62 @@
 
   // Subnets
-  $DbResult2 = $Database->select('NetworkSubnet', '*', 'Member='.$Member['Id']);
+  $DbResult2 = $Database->select('NetworkSubnet', '*', '`Member`='.$Member['Id']);
   while($Subnet = $DbResult2->fetch_assoc())
   {   

trunk/system/generators/netwatch.php

@@ -23 +23 @@
     $I = explode('.', $Subnet['AddressRange']);
     $I = $I[2];
-    $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 (NetworkDevice.Used = 1) ORDER BY NetworkInterface.LocalIP');
+    $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 (`NetworkDevice`.`Used` = 1) ORDER BY `NetworkInterface`.`LocalIP`');
     while($Interface = $DbResult2->fetch_assoc())
     {

trunk/system/generators/netwatch_import.php

@@ -6 +6 @@
 include('common.php');
 $Path = array('tool', 'netwatch');
+$Period = 60; // every 60 seconds
 
-$DbResult3 = $Database->query('SELECT * FROM `NetworkSubnet`  WHERE `Member` = 0');
+while(1)
+{
+  $StartTime = time();
+
+$DbResult3 = $Database->query('SELECT * FROM `NetworkSubnet` WHERE `Member` IS NULL');
+//echo($DbResult3->num_rows);
 while($Subnet = $DbResult3->fetch_assoc())
 {
@@ -39 +45 @@
       }
 
-      $DbResult = $Database->update('NetworkInterface', 'LocalIP = "'.$Properties['host'].'"', $Values);
+      $DbResult = $Database->update('NetworkInterface', '`LocalIP` = "'.$Properties['host'].'"', $Values);
       if($Online == 1)
       {
-        $DbResult = $Database->select('NetworkInterface', 'Id', 'LocalIP = "'.$Properties['host'].'"');
+        $DbResult = $Database->select('NetworkInterface', 'Id', '`LocalIP` = "'.$Properties['host'].'"');
         $DbRow = $DbResult->fetch_assoc();
         $InterfaceId = $DbRow['Id'];
-        $DbResult2 = $Database->select('NetworkInterfaceStat', '*', '(NetworkInterface="'.$InterfaceId.'") AND (Time = DATE_FORMAT(NOW(), "%Y-%m-%d %H:00:00"))');
-        if($DbResult2->num_rows == 0) $Database->query('REPLACE INTO NetworkInterfaceStat (NetworkInterface, Time) VALUES ("'.$InterfaceId.'", DATE_FORMAT(NOW(), "%Y-%m-%d %H:00:00"))');          
-        $Database->query('UPDATE NetworkInterfaceStat SET PingCount = PingCount + 1 WHERE (NetworkInterface="'.$InterfaceId.'") AND (Time = DATE_FORMAT(NOW(), "%Y-%m-%d %H:00:00"))');
+        $DbResult2 = $Database->select('NetworkInterfaceStat', '*', '(`NetworkInterface`="'.$InterfaceId.'") AND (`Time` = DATE_FORMAT(NOW(), "%Y-%m-%d %H:00:00"))');
+        if($DbResult2->num_rows == 0) $Database->query('REPLACE INTO `NetworkInterfaceStat` (`NetworkInterface`, `Time`) VALUES ("'.$InterfaceId.'", DATE_FORMAT(NOW(), "%Y-%m-%d %H:00:00"))');          
+        $Database->query('UPDATE `NetworkInterfaceStat` SET `PingCount` = `PingCount` + 1 WHERE (`NetworkInterface`="'.$InterfaceId.'") AND (`Time` = DATE_FORMAT(NOW(), "%Y-%m-%d %H:00:00"))');
         
       }
@@ -55 +61 @@
 
 // Update device online state
-$DbResult = $Database->select('NetworkDevice', 'Id', 'Used=1');
+$DbResult = $Database->select('NetworkDevice', 'Id', '`Used`=1');
 while($Device = $DbResult->fetch_assoc())
 {
-  $DbResult2 = $Database->select('NetworkInterface', 'SUM(Online)', 'Device='.$Device['Id']);
+  $DbResult2 = $Database->select('NetworkInterface', 'SUM(`Online`) AS `SumOnline`', '`Device`='.$Device['Id']);
   $DbRow = $DbResult2->fetch_array();
-  if($DbRow['SUM(Online)'] > 0) $Online = 1; else $Online = 0;  
+  if($DbRow['SumOnline'] > 0) $Online = 1; else $Online = 0;  
   $Database->update('NetworkDevice', 'Id='.$Device['Id'], array('Online' => $Online));
   if($Online == 1) $Database->update('NetworkDevice', 'Id='.$Device['Id'], array('LastOnline' => 'NOW()'));
 }
 
+  $EndTime = time();
+  $Delay = $Period - ($EndTime - $StartTime);
+  if($Delay < 0) $Delay = 0;
+
+  echo('Waiting '.$Delay.' seconds...'."\n");
+  sleep($Delay);
+}
+
 ?>

trunk/system/generators/queue.php

@@ -69 +69 @@
   $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']);
+  $DbResult2 = $Database->select('NetworkDevice', 'COUNT(*)', '(`Used` = 1) AND (`Member`='.$Member['Id'].')');
   $Row = $DbResult2->fetch_row();
   $HostCount = $Row[0];

trunk/system/routerboard.php

@@ -49 +49 @@
     if($Commands != '')
     {
-      $Output = array();
       $Commands = addslashes($Commands);
       $Commands = str_replace('$', '\$', $Commands);
+      //$Commands = str_replace(' ', '\ ', $Commands);  
       $Command = $this->SSHPath.' -o ConnectTimeout='.$this->Timeout.' -l '.$this->UserName.' -i '.$this->PrivateKey.' '.$this->HostName.' "'.$Commands.'"';
       if($this->Debug) echo($Command);
+      $Output = array();
       exec($Command, $Output);
     } else $Output = '';