[lms] Re: dwa etapy cutoff

9 Sty 2006


      Użytkownik tombog napisał:
...
*This message was transferred with a trial version of CommuniGate(tm) Pro*
Chętnie bym się przyjrzał zmianom. Może coś by wniosły do pomysłu.
W pliku opisane jest co i jak. Rozwiazanie dziala, ale nie jestem specem 
od zapytan do baz danych wiec jezeli ktos sie tym zainteresuje to mozna 
byloby to poprawic.
Mozna rowniez umiescic w lms.ini date od kiedy ma byc powiadamianie i 
ktorego dnia ma zablokowac dostep do internetu.
Jako ze nie uzywamy squida wiec przerobilem sobie lms-squid i na samym 
koncu petli while dodalem sobie:
if ($row->{'warning'}==0)
{
    system("sudo iptables ... regulka usuwajaca przekierowanie na proxy");
}
Oczywiscie uzytkownik proxy musi miec dostep do iptables wiec nalezy go 
dodac do sudo.
Pozniej gdy lms-warningson-cutoff po ktoryms odswierzeniu (np co 30 
minut) natrafi na flage warning=1(czyli uzytkownik nie odczytal 
wiadomosci) wtedy generuje regulke usuwajaca a dopiero pozniej regulke 
dolaczajaca.
Pozdrawiam :)
Lukasz
#!/usr/bin/perl -Tw
#
# LMS version 1.6.5 Druhin
#
#  (C) 2001-2005 LMS Developers
#
#  Please, see the doc/AUTHORS for more information about authors!
#
#  This program is free software; you can redistribute it and/or modify
#  it under the terms of the GNU General Public License Version 2 as
#  published by the Free Software Foundation.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program; if not, write to the Free Software
#  Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307,
#  USA.
#
#  $Id: lms-warningson-cutoff,v 1.00.0.0 2006/01/05 17:38:40 alec Exp $
use strict;
use DBI;
use Config::IniFiles;
use Getopt::Long;
use vars qw($configfile $quiet $help $version);
use POSIX qw(strftime);
sub u32todotquad($)
{
    my $p = shift @_;
    return sprintf "%d.%d.%d.%d",($p>>24)&0xff,($p>>16)&0xff,($p>>8)&0xff,$p&0xff;
}
my $_version = '1.6.5 Druhin';
my %options = (
    "--config-file|C=s"	=>	$configfile,
    "--quiet|q"		=>	$quiet,
    "--help|h"		=>	$help,
    "--version|v"		=>	$version,
);
Getopt::Long::config("no_ignore_case");
GetOptions(%options);
if($help)
{
    print STDERR <<EOF;
lms-warningson-cutoff, version $_version
(C) 2001-2005 LMS Developers
-C, --config-file=/etc/lms/lms.ini	alternate config file (default: /etc/lms/lms.ini);
-h, --help			print this help and exit;
-v, --version			print version info and exit;
-q, --quiet			suppress any output, except errors;
EOF
    exit 0;
}
if($version)
{
    print STDERR <<EOF;
lms-warnigson-cutoff, version $_version
(C) 2001-2005 LMS Developers
EOF
    exit 0;
}
if(!$configfile)
{
    $configfile = "/etc/lms/lms.ini";
}
if(!$quiet)
{
    print STDOUT "lms-warningson-cutoff, version $_version\n";
    print STDOUT "(C) 2001-2005 LMS Developers\n";
    print STDOUT "Using file $configfile as config.\n";
}
if(! -r $configfile)
{
    print STDERR "Fatal error: Unable to read configuration file $configfile, exiting.\n";
    exit 1;
}
my $ini = new Config::IniFiles -file => $configfile;
print @Config::IniFiles::errors;
my $dbtype = $ini->val('database', 'type') || 'mysql';
my $dbhost = $ini->val('database', 'host') || 'localhost';
my $dbuser = $ini->val('database', 'user') || 'root';
my $dbpasswd = $ini->val('database', 'password') || '';
my $dbname = $ini->val('database', 'database') || 'lms';
my $dbase;
my $utsfmt;
open(WARNFILE,">/home/kleju/warningson-proxy") or die("Sorry nie mozna utworzyc pliku");
if($dbtype eq "mysql")
{
    $dbase = DBI->connect("DBI:mysql:database=$dbname;host=$dbhost","$dbuser","$dbpasswd", { RaiseError => 1 });
    $utsfmt = "UNIX_TIMESTAMP()";
}
elsif($dbtype eq "postgres")
{
    $dbase = DBI->connect("DBI:Pg:dbname=$dbname;host=$dbhost","$dbuser","$dbpasswd", { RaiseError => 1 });
    $utsfmt = "EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))";
}
elsif($dbtype eq "sqlite")
{
    $dbase = DBI->connect("DBI:SQLite:dbname=$dbname;host=$dbhost","$dbuser","$dbpasswd", { RaiseError => 1 });
    $utsfmt = "strftime('%s','now')";	
}
else
{
    print STDERR "Fatal error: unsupported database type: $dbtype, exiting.\n";
    exit 1;
}
my $dbq = $dbase->prepare("SELECT time, userid AS id FROM cash WHERE adminid = '0'");
$dbq->execute();
while (my $row = $dbq->fetchrow_hashref())
{			
    		# $days pobiera aktualny dzien, $payments_day zawiera rok i miesiac naliczenia faktury + zmienna $days
    		# $nowa_date zawiera aktualna date z dniem 11
    		# $date_warningson zawiera liczbe po ktorej pozniej bedzie ustawiana flaga warning			
    		my $days = strftime "%d", localtime;
    		my $payments_date = strftime "%Y%m$days", localtime($row->{'time'});
    		my $now_date = strftime "%Y%m11", localtime;
    					
    		my $date_warningson = $now_date-$payments_date;
    		
    		if ($date_warningson <= 0 && $date_warningson > -1) 
    		{
    		
    		    # zapisywanie do pliku tych ktorzy maja warning=1 ale reguly usuwajacej proxy
    		    # w przypadku gdy dana osob nie odczyta ostrzezenia czyli zmodyfikowana wersja lms-squid nie usunie
    		    # regulki proxy
    		    my $sdbq2 = $dbase->prepare("SELECT mac FROM nodes WHERE ownerid='$row->{'id'}' AND warning='1'");
    		    $sdbq2->execute();
    		    
    		    while(my $row2b = $sdbq2->fetchrow_hashref())
    		    {
    			print WARNFILE "/sbin/iptables -t nat -D PREROUTING -m mac --mac-source $row2b->{'mac'} -p tcp --dport 80 -j REDIRECT --to-port 8080\n\n";
    		    }
    		
    		    my $sdbq = $dbase->prepare("UPDATE nodes SET warning=1 WHERE ownerid='$row->{'id'}'");
    		    $sdbq->execute();
    		    
    		    if(!$quiet)
    		    {
    			my $tdbq = $dbase->prepare("SELECT lastname, name FROM users WHERE id='$row->{'id'}'");
    			$tdbq->execute();
    		    
    			while (my $row2 = $tdbq->fetchrow_hashref())
    			{
    			    print STDOUT "Ostrzezenie dla $row2->{'lastname'} $row2->{'name'} id: $row->{'id'}\n";
    			}
    		    }
    		}
    		
    		# $date_cutoff jest data odciecia, $date_now jest data pobierana w danej chwili
    		# obydwie zmienne sa porownywane ze soba, jezeli sie zgadzaja to nastepuje zmiana
    		# flagi access na 0
    		my $date_cutoff = strftime "%Y%m28", localtime;
    		my $date_now = strftime "%Y%m%d", localtime;
    		
    		if ($date_cutoff == $date_now)
    		{
    		    my $fdbq = $dbase->prepare("UPDATE nodes SET warning=0, access=0 WHERE ownerid='$row->{'id'}'");
    		    $fdbq->execute();
    		    
    		    my $fidpq = $dbase->prepare("SELECT lastname, name FROM users WHERE id='$row->{'id'}'");
    		    $fidpq->execute();
    		    
    		    while (my $row3 = $fidpq->fetchrow_hashref())
    		    {
    			print STDOUT "Blokada dla $row3->{'lastname'} $row3->{'name'} id: $row->{'id'}\n";
    		    }
    		}
    		
    		# zapisywanie do pliku, tych ktorzy maja oznaczenie na warning=1
    		my $sdbq3 = $dbase->prepare("SELECT mac FROM nodes WHERE ownerid='$row->{'id'}' AND warning='1'");
    		$sdbq3->execute();
    		    
    		while(my $row3b = $sdbq3->fetchrow_hashref())
    		{
    		    print WARNFILE "/sbin/iptables -t nat -A PREROUTING -m mac --mac-source $row3b->{'mac'} -p tcp --dport 80 -j REDIRECT --to-port 8080\n\n";
    		}
}
$dbase->disconnect();
close(WARNFILE);

[lms] Re: dwa etapy cutoff

Łukasz Klejnberg