lms-tariff_per_node.patch

   1 diff -ur lms-cvs.orig/lib/LMS.class.php lms-cvs/lib/LMS.class.php
   2 --- lms-cvs.orig/lib/LMS.class.php      2005-02-02 21:09:04.226695078 +0100
   3 +++ lms-cvs/lib/LMS.class.php   2005-02-02 21:08:30.677015322 +0100
   4 @@ -1221,6 +1221,12 @@
   5                 return $this->DB->GetOne('SELECT ownerid FROM nodes WHERE id=?', array($id));
   6         }
   7
   8 +       function NodeAssignmentUpdate($nodedata)
   9 +       {
  10 +               $this->SetTS('nodesassignments');
  11 +               return $this->DB->Execute('UPDATE nodesassignments SET assignmentid=? WHERE nodeid=?',array($nodedata['assignment'],$nodedata['id']));
  12 +       }
  13 +
  14         function NodeUpdate($nodedata)
  15         {
  16                 $this->SetTS('nodes');
  17 @@ -1272,7 +1278,17 @@
  18         {
  19                 return $this->DB->GetOne('SELECT name FROM nodes WHERE ipaddr=inet_aton(?)', array($ipaddr));
  20         }
  21 +
  22 +       function GetNodeAssignment($id)
  23 +       {
  24 +               return $this->DB->GetOne('SELECT assignmentid FROM nodesassignments WHERE nodeid=?',array($id));
  25 +       }
  26
  27 +       function GetNodeTariff($id)
  28 +       {
  29 +               return $this->DB->GetOne('SELECT name FROM tariffs LEFT JOIN assignments ON (tariffs.id=assignments.tariffid) WHERE assignments.id=?',array($id));
  30 +       }
  31 +
  32         function GetNode($id)
  33         {
  34                 if($result = $this->DB->GetRow('SELECT id, name, ownerid, ipaddr, inet_ntoa(ipaddr) AS ip, mac, access, warning, creationdate, moddate, creatorid, modid, netdev, lastonline, info FROM nodes WHERE id=?', array($id)))
  35 @@ -1296,6 +1312,8 @@
  36                         $result['netname'] = $this->GetNetworkName($result['netid']);
  37                         $result['producer'] = get_producer($result['mac']);
  38                         $result['devicename'] = $this->GetNetDevName($result['netdevid']);
  39 +                       $result['assignment'] = $this->GetNodeAssignment($id);
  40 +                       $result['tariff'] = $this->GetNodeTariff($result['assignment']);
  41                         return $result;
  42                 }else
  43                         return FALSE;
  44 @@ -1488,6 +1506,12 @@
  45                         return $this->DB->Execute('UPDATE nodes SET access=0 WHERE netdev=? AND ownerid=0', array($netdev));
  46         }
  47
  48 +       function NodeAssignmentAdd($nodedata)
  49 +       {
  50 +               $this->SetTS('nodesassignments');
  51 +               return $this->DB->Execute('INSERT INTO nodesassignments (nodeid,assignmentid) VALUES (?,?)',array($nodedata['id'], $nodedata['assignment']));
  52 +       }
  53 +
  54         function NodeAdd($nodedata)
  55         {
  56                 $this->SetTS('nodes');
  57 diff -ur lms-cvs.orig/modules/nodeadd.php lms-cvs/modules/nodeadd.php
  58 --- lms-cvs.orig/modules/nodeadd.php    2005-02-02 21:11:40.954932764 +0100
  59 +++ lms-cvs/modules/nodeadd.php 2005-02-01 20:51:45.000000000 +0100
  60 @@ -81,7 +81,8 @@
  61
  62         if(!$error)
  63         {
  64 -               $nodeid = $LMS->NodeAdd($nodedata);
  65 +               $nodeid=$LMS->NodeAdd($nodedata);
  66 +               $LMS->NodeAssignmentAdd($nodedata);
  67                 if($nodedata['reuse']=='')
  68                 {
  69                         header('Location: ?m=nodeinfo&id='.$nodeid);
  70 diff -ur lms-cvs.orig/modules/nodeedit.php lms-cvs/modules/nodeedit.php
  71 --- lms-cvs.orig/modules/nodeedit.php   2005-02-02 21:11:41.036919912 +0100
  72 +++ lms-cvs/modules/nodeedit.php        2005-02-01 20:51:45.000000000 +0100
  73 @@ -130,6 +130,8 @@
  74
  75         if(!$error)
  76         {
  77 +               if ($nodeinfo['assignment']) $LMS->NodeAssignmentUpdate($nodeedit);
  78 +               else $LMS->NodeAssignmentAdd($nodeedit);
  79                 $LMS->NodeUpdate($nodeedit);
  80                 header('Location: ?m=nodeinfo&id='.$nodeedit['id']);
  81         }
  82 diff -ur lms-cvs.orig/templates/nodeaddbox.html lms-cvs/templates/nodeaddbox.html
  83 --- lms-cvs.orig/templates/nodeaddbox.html      2005-01-31 22:09:20.000000000 +0100
  84 +++ lms-cvs/templates/nodeaddbox.html   2005-02-01 20:52:10.000000000 +0100
  85 @@ -35,6 +35,22 @@
  86         </TR>
  87         <TR CLASS="LIGHT">
  88                 <TD WIDTH="1%" CLASS="FLEFT">
  89 +               <IMG SRC="img/money.gif" BORDER="0" ALT=""></A>
  90 +               </TD>
  91 +               <TD WIDTH="1%" NOWRAP>
  92 +                       <B>{t}Tariff:{/t}</B>
  93 +               </TD>
  94 +               <TD WIDTH="99%" CLASS="FRIGHT" COLSPAN="2">
  95 +                       <SELECT SIZE="1" NAME="nodeedit[assignment]" {tip text="Pick tariff from list" trigger="assigment"}>
  96 +                               <OPTION VALUE="0" {if ! $nodeinfo.assignment}SELECTED{/if}>{t}none{/t}</OPTION>
  97 +                               {section name="assignment" loop=$assignments}
  98 +                               <OPTION VALUE="{$assignments[assignment].id}" {if $nodeinfo.assignment == $assignments[assignment].id} SELECTED {/if}>{$assignments[assignment].name} ({$assignments[assignment].id})</OPTION>
  99 +                               {/section}
 100 +                       </SELECT>
 101 +               </TD>
 102 +       </TR>
 103 +       <TR CLASS="LIGHT">
 104 +               <TD WIDTH="1%" CLASS="FLEFT">
 105                         <IMG SRC="img/{if ! $nodedata.access}no{/if}access.gif" BORDER="0" ALT="">
 106                 </TD>
 107                 <TD WIDTH="1%" NOWRAP>
 108 diff -ur lms-cvs.orig/templates/nodeeditbox.html lms-cvs/templates/nodeeditbox.html
 109 --- lms-cvs.orig/templates/nodeeditbox.html     2005-01-07 21:26:18.000000000 +0100
 110 +++ lms-cvs/templates/nodeeditbox.html  2005-01-14 22:10:19.000000000 +0100
 111 @@ -49,6 +49,20 @@
 112         </TR>
 113         <TR CLASS="LIGHT">
 114                 <TD WIDTH="1%" CLASS="FLEFT">
 115 +                       <IMG SRC="img/money.gif" BORDER="0" ALT=""></A>
 116 +               </TD>
 117 +               <TD WIDTH="99%" CLASS="FRIGHT" COLSPAN="2">
 118 +                       <SELECT SIZE="1" NAME="nodeedit[assignment]" {tip text="Pick tariff from list" trigger="assigment"}>
 119 +                               <OPTION VALUE="0" {if ! $nodeinfo.assignment}SELECTED{/if}>{t}none{/t}</OPTION>
 120 +                               {section name="assignment" loop=$assignments}
 121 +                               <OPTION VALUE="{$assignments[assignment].id}" {if $nodeinfo.assignment == $assignments[assignment].id} SELECTED {/if}>{$assignments[assignment].name} ({$assignments[assignment].id})</OPTION>
 122 +                               {/section}
 123 +
 124 +                       </SELECT>
 125 +               </TD>
 126 +       </TR>
 127 +       <TR CLASS="LIGHT">
 128 +               <TD WIDTH="1%" CLASS="FLEFT">
 129                         <IMG SRC="img/{if ! $nodeinfo.access}no{/if}access.gif" BORDER="0" ALT="">
 130                 </TD>
 131                 <TD WIDTH="99%" CLASS="FRIGHT" COLSPAN="2">
 132 diff -ur lms-cvs.orig/templates/nodeinfobox.html lms-cvs/templates/nodeinfobox.html
 133 --- lms-cvs.orig/templates/nodeinfobox.html     2005-01-20 18:53:16.000000000 +0100
 134 +++ lms-cvs/templates/nodeinfobox.html  2005-02-02 21:18:44.702467494 +0100
 135 @@ -57,6 +57,14 @@
 136         </TR>
 137         <TR CLASS="LIGHT">
 138                 <TD WIDTH="1%" CLASS="FLEFT">
 139 +                       <IMG SRC="img/money.gif" BORDER="0" ALT=""></A>
 140 +               </TD>
 141 +               <TD WIDTH="99%" CLASS="FRIGHT" COLSPAN="2">
 142 +                       <B>{if $nodeinfo.assignment}{$nodeinfo.tariff}({$nodeinfo.assignment|string_format:"%04d"}){else}{t}none{/t}{/if}</B>
 143 +               </TD>
 144 +       </TR>
 145 +       <TR CLASS="LIGHT">
 146 +               <TD WIDTH="1%" CLASS="FLEFT">
 147                         <A HREF="?m=nodeset&id={$nodeinfo.id}"><IMG SRC="img/{if ! $nodeinfo.access}no{/if}access.gif" BORDER="0" ALT=""></A>
 148                 </TD>
 149                 <TD WIDTH="99%" CLASS="FRIGHT" COLSPAN="2">
 150 @@ -108,8 +116,8 @@
 151         <TR CLASS="LIGHT">
 152                 <TD WIDTH="100%" CLASS="FBOTTOMU" COLSPAN="3" ALIGN="RIGHT">
 153                         <A HREF="?m=userinfo&id={$nodeinfo.ownerid}">{t}All nodes{/t} <IMG SRC="img/cancel.gif" BORDER="0" ALT="" ALIGN="absmiddle"></A>
 154 -                       <A HREF="?m=nodedel&id={$nodeinfo.id}" onClick="return confirmLink(this, '{t 0=$nodeinfo.name|upper}Are you sure, you want to remove node \'$0\' from database?{/t}')">{t}Delete{/t} <IMG SRC="img/delete.gif" BORDER="0" ALT="" ALIGN="absmiddle"></A>
 155 -                       <A HREF="?m=nodeedit&id={$nodeinfo.id}" ACCESSKEY="E">{t}Edit{/t} <IMG SRC="img/edit.gif" BORDER="0" ALT="" ALIGN="absmiddle"></A>
 156 -               </TD>
 157 -       </TR>
 158 +                       <A HREF="?m=nodedel&id={$nodeinfo.id}" onClick="return confirmLink(this, '{t}{$nodeinfo.name|upper}Are you sure, you want to remove node \'$0\' from database?{/t}')">{t}Delete{/t} <IMG SRC="img/delete.gif" BORDER="0" ALT="" ALIGN="absmiddle"></A>
 159 +               <A HREF="?m=nodeedit&id={$nodeinfo.id}" ACCESSKEY="E">{t}Edit{/t} <IMG SRC="img/edit.gif" BORDER="0" ALT="" ALIGN="absmiddle"></A>
 160 +       </TD>
 161 +</TR>
 162  </TABLE>
 163 diff -ur lms-cvs.orig/templates/userassignments.html lms-cvs/templates/userassignments.html
 164 --- lms-cvs.orig/templates/userassignments.html 2005-01-31 22:09:24.000000000 +0100
 165 +++ lms-cvs/templates/userassignments.html      2005-02-01 20:52:11.000000000 +0100
 166 @@ -27,7 +27,7 @@
 167                                         {if !$a.tariffid}
 168                                         <TD WIDTH="8%" COLSPAN="8"><B>{t}Suspending of all liabilities{/t}</B></TD>
 169                                         {else}
 170 -                                       <TD WIDTH="1%" NOWRAP OnClick="return self.location.href='?m=userassignmentsedit&id={$a.id}'"><B><A HREF="?m=tariffinfo&id={$a.tariffid}">{$a.name}</A></B></TD>
 171 +                                       <TD WIDTH="1%" NOWRAP OnClick="return self.location.href='?m=userassignmentsedit&id={$a.id}'"><B><A HREF="?m=tariffinfo&id={$a.tariffid}">{$a.name} ({$a.id|string_format:"%04d"})</A></B></TD>
 172                                         <TD WIDTH="1%" NOWRAP ALIGN="RIGHT" OnClick="return self.location.href='?m=userassignmentsedit&id={$a.id}'">{$a.value|money_format}</TD>
 173                                         <TD WIDTH="1%" NOWRAP OnClick="return self.location.href='?m=userassignmentsedit&id={$a.id}'">{$a.period} ({$a.at})</TD>
 174                                         <TD WIDTH="1%" NOWRAP ALIGN="RIGHT" OnClick="return self.location.href='?m=userassignmentsedit&id={$a.id}'">{$a.downrate|default:"-"} kbps</TD>
 175 --- lms/bin/lms-traffic-htbiptlimits    2005-02-16 10:00:11.000000000 +0100
 176 +++ lms/bin/lms-traffic-htbiptlimits    2005-02-19 08:58:07.000000000 +0100
 177 @@ -173,6 +173,7 @@
 178         exit 1;
 179  }
 180
 181 +system("cp $outfile $outfile.sav");
 182  open(OUTFILE, ">$outfile") or die("Fatal error: Unable to write $outfile, exiting.\n");
 183
 184  my @local_ports_list = split(' ',$local_ports);
 185 @@ -251,13 +252,13 @@
 186         my $prefix = mask2prefix($row->{'mask'});
 187         my $netsize = 2**(32 - $prefix);
 188         my $netend = $netstart + $netsize - 1;
 189 -       my $sdbq = $dbase->prepare("SELECT ipaddr, ownerid FROM nodes WHERE ipaddr >= $netstart AND ipaddr <= $netend");
 190 +       my $sdbq = $dbase->prepare("SELECT ipaddr, assignmentid FROM nodes,nodesassignments WHERE nodes.id=nodesassignments.nodeid AND ipaddr >= $netstart AND ipaddr <= $netend");
 191         $sdbq->execute();
 192         while (my $srow = $sdbq->fetchrow_hashref())
 193         {
 194                 my $ipaddr = u32todotquad($srow->{'ipaddr'});
 195 -               $nodetable{$srow->{'ownerid'}} = "" if not defined $nodetable{$srow->{'ownerid'}};
 196 -               $nodetable{$srow->{'ownerid'}} = $nodetable{$srow->{'ownerid'}} . " " . $ipaddr . ":" . $row->{'interface'};
 197 +               $nodetable{$srow->{'assignmentid'}} = "" if not defined $nodetable{$srow->{'assignmentid'}};
 198 +               $nodetable{$srow->{'assignmentid'}} = $nodetable{$srow->{'assignmentid'}} . " " . $ipaddr . ":" . $row->{'interface'};
 199                 $iftable{$row->{'interface'}} = $totalspd if not defined $iftable{$row->{'interface'}};
 200         }
 201  }
 202 @@ -271,19 +272,24 @@
 203
 204  }
 205
 206 -$dbq = $dbase->prepare("SELECT userid, SUM(uprate) AS uprate, SUM(downrate) AS downrate, SUM(upceil) AS upceil, SUM(downceil) AS downceil FROM assignments, users, tariffs WHERE users.id = userid AND deleted = 0 AND tariffid = tariffs.id AND (datefrom <= $utsfmt OR datefrom = 0) AND (dateto > $utsfmt OR dateto = 0) GROUP BY userid");
 207 +$dbq = $dbase->prepare("SELECT nodes.access AS access, assignments.id AS assignmentid, users.name AS name,users.lastname AS lastname, tariffs.uprate AS uprate, tariffs.downrate AS downrate,tariffs.upceil AS upceil, tariffs.downceil AS downceil FROM nodesassignments, assignments, tariffs, users, nodes WHERE nodesassignments.nodeid=nodes.id AND nodesassignments.assignmentid=assignments.id AND assignments.tariffid=tariffs.id AND assignments.userid=users.id AND (datefrom <= $utsfmt OR datefrom = 0) AND (dateto > $utsfmt OR dateto = 0) GROUP BY assignments.id");
 208  $dbq->execute();
 209
 210  my $iptid = 0;
 211  my %htbid;
 212 +my $uprate = 0;
 213 +my $upceil = 0;
 214 +my $downrate = 0;
 215 +my $downceil = 0;
 216
 217  while (my $row = $dbq->fetchrow_hashref())
 218  {
 219 -       if($nodetable{$row->{'userid'}} && $row->{'downrate'})
 220 +       print OUTFILE "### ".$row->{'name'}." ".$row->{'lastname'}."\n";
 221 +       if($nodetable{$row->{'assignmentid'}} && $row->{'downrate'})
 222         {
 223                 my %ifaces;
 224                 $iptid ++;
 225 -               foreach my $ippair (split(' ',$nodetable{$row->{'userid'}}))
 226 +               foreach my $ippair (split(' ',$nodetable{$row->{'assignmentid'}}))
 227                 {
 228                         my ($ipaddr, $if) = split(':',$ippair);
 229                         $ifaces{$if} = 1;
 230 @@ -293,14 +299,24 @@
 231                 {
 232                         $htbid{$key} = 0 if not defined $htbid{$key};
 233                         $htbid{$key} ++;
 234 -                       print OUTFILE "$tc_binary class add dev $key parent 1:19999 classid 1:".$htbid{$key}." htb rate ".$row->{'downrate'}."kbit ceil ".$row->{'downceil'}."kbit burst ".($row->{'downrate'} / 8)."kbit\n";
 235 +                       if ($row->{'access'})
 236 +                       {
 237 +                               $downrate=$row->{'downrate'};
 238 +                               $downceil=$row->{'downceil'};
 239 +                       }
 240 +                       else
 241 +                       {
 242 +                                $downrate=$row->{'downrate'}/100;
 243 +                                $downceil=$row->{'downceil'}/100;
 244 +                       }
 245 +                       print OUTFILE "$tc_binary class add dev $key parent 1:19999 classid 1:".$htbid{$key}." htb rate ".$downrate."kbit ceil ".$downceil."kbit burst ".($downrate / 8)."kbit\n";
 246                         print OUTFILE "$tc_binary filter add dev $key protocol ip parent 1: handle $iptid fw classid 1:".$htbid{$key}."\n";
 247                 }
 248         }
 249 -       if($nodetable{$row->{'userid'}} && $row->{'uprate'})
 250 +       if($nodetable{$row->{'assignmentid'}} && $row->{'uprate'})
 251         {
 252                 $iptid ++;
 253 -               foreach my $ippair (split(' ',$nodetable{$row->{'userid'}}))
 254 +               foreach my $ippair (split(' ',$nodetable{$row->{'assignmentid'}}))
 255                 {
 256                         my ($ipaddr, $if) = split(':',$ippair);
 257                         print OUTFILE "$iptables_binary -t mangle -A LIMITS -s $ipaddr -j MARK --set-mark $iptid\n";
 258 @@ -310,7 +326,17 @@
 259                         my ($if,$spd) = split(':', $wan_interface);
 260                         $htbid{$if} = 0 if not defined $htbid{$if};
 261                         $htbid{$if} ++;
 262 -                       print OUTFILE "$tc_binary class add dev $if parent 1:19999 classid 1:".$htbid{$if}." htb rate ".$row->{'uprate'}."kbit ceil ".$row->{'upceil'}."kbit burst ".($row->{'uprate'} / 8). "kbit\n";
 263 +                        if ($row->{'access'})
 264 +                        {
 265 +                                $uprate=$row->{'uprate'};
 266 +                                $upceil=$row->{'upceil'};
 267 +                        }
 268 +                        else
 269 +                        {
 270 +                                $uprate=$row->{'uprate'}/100;
 271 +                                $upceil=$row->{'upceil'}/100;
 272 +                        }
 273 +                       print OUTFILE "$tc_binary class add dev $if parent 1:19999 classid 1:".$htbid{$if}." htb rate ".$uprate."kbit ceil ".$upceil."kbit burst ".($uprate / 8). "kbit\n";
 274                         print OUTFILE "$tc_binary filter add dev $if protocol ip parent 1: handle $iptid fw classid 1:".$htbid{$if}."\n";
 275                 }
 276         }