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);
