Код: Выделить всё
#!/usr/bin/perl
use Mysql;
$prefix = '/netup'; @force = grep {/--force/} @ARGV; @drop = grep {/--drop/} @ARGV;
open CONFIG, "$prefix/utm5/utm5.cfg"; @config = <CONFIG>; close CONFIG; foreach $line (@config) { if ($line =~ m/^([^#].+?)=(.*)$/) {$$1 = $2} }
$db = Mysql->connect($database_host, $database, $database_login, $database_password); $db->Query("SET NAMES koi8r");
create_payments_updates_table();
$sth = $db->query("SELECT COUNT(*) AS num_rows FROM payments_updates"); %foo = $sth->fetchhash; if($foo{num_rows} == 0){$clean = 1} else {$clean = 0}
if($#force == -1){ my $sth = $db->query("SELECT MAX(user_change_date) AS last_update FROM payments_updates");
my %bar = $sth->fetchhash; $last_users_change = $bar{last_update} + 0 } else {$last_users_change = 0}
if($#force == -1){ my $sth = $db->query("SELECT MAX(payment_date) AS last_payment_date FROM payments_updates");
my %baz = $sth->fetchhash; $last_payment_date = $baz{last_payment_date} + 0 } else {$last_payment_date = 0}
fill_list($last_users_change); if($clean != 0) {traverse_history()}
update_payments("UTM5.payment_transactions", $last_payment_date);
#===========================================================================================
sub traverse_history {
my $sth = $db->query("SELECT table_name FROM archives WHERE table_type = 7 ORDER BY start_date ASC") or die $Mysql::db_errstr;
while (%archives = $sth->fetchhash){ update_payments("$archives{table_name}", 0); }
}
sub update_payments { my ($ptr_table, $date) = @_; print "update from $ptr_table\n";
my $sth = $db->query("SELECT account_id, actual_date FROM $ptr_table WHERE actual_date > $date GROUP BY account_id HAVING MAX(actual_date)") or die $Mysql::db_errstr;
while (%payments = $sth->fetchhash){ $sth1 = $db->query("SELECT payment_date FROM payments_updates WHERE id=$payments{account_id}");
%foo = $sth1->fetchhash; if($foo{payment_date} >= $payments{actual_date}) {next}
$db->query("UPDATE payments_updates SET payment_date = $payments{actual_date} WHERE id = $payments{account_id}") or die $Mysql::db_errstr;}
}
sub fill_list { my ($last_update) = @_; print "fill accounts list\n";
my $sth = $db->query("SELECT accounts.id,
users.login,
users.full_name,
GROUP_CONCAT(INET_NTOA(ip_groups.ip & 4294967295) SEPARATOR ' ') AS ip,
MAX(ip_groups.create_date) AS ip_create_date,
MAX(ip_groups.delete_date) AS ip_delete_date,
accounts.balance,
accounts.credit,
accounts.unlimited,
accounts.is_blocked,
accounts.is_deleted,
users.last_change_date,
account_tariff_link.tariff_id,
payments_updates.user_change_date,
payments_updates.payment_date
FROM accounts
LEFT JOIN users_accounts ON users_accounts.account_id = accounts.id
LEFT JOIN users ON users.id = users_accounts.uid
LEFT JOIN service_links ON service_links.account_id = accounts.id
LEFT JOIN iptraffic_service_links ON iptraffic_service_links.id = service_links.id
LEFT JOIN ip_groups ON ip_groups.ip_group_id = iptraffic_service_links.ip_group_id
LEFT JOIN account_tariff_link ON account_tariff_link.account_id = accounts.id
LEFT JOIN payments_updates ON payments_updates.id = accounts.id
WHERE (ip_groups.ip IS NULL OR (ip_groups.is_deleted = 0 AND (ip_groups.ip_type & 0x1) = 0x0 AND ip_groups.mask = -1))
GROUP BY accounts.id
ORDER BY accounts.id") or die $Mysql::db_errstr;
while (%accounts = $sth->fetchhash){
if($accounts{payment_date} eq ''){ $accounts{payment_date} = -1; } if($accounts{tariff_id} eq ''){ $accounts{tariff_id} = -1; }
$last_change = max($accounts{ip_create_date}, $accounts{ip_delete_date}, $accounts{last_change_date});
if($accounts{user_change_date} < $last_change or $#force > -1) { print "$accounts{login}\n";
$db->query("REPLACE INTO payments_updates
SET id = $accounts{id},
login = '$accounts{login}',
full_name = '$accounts{full_name}',
ip = '$accounts{ip}',
balance = $accounts{balance},
credit = $accounts{credit},
tariff_id = $accounts{tariff_id},
unlimited = $accounts{unlimited},
is_blocked = $accounts{is_blocked},
is_deleted = $accounts{is_deleted},
user_change_date = $last_change,
payment_date = $accounts{payment_date}") or die $Mysql::db_errstr;
}
}
}
sub create_payments_updates_table { if($#drop > -1){ $db->query("DROP TABLE payments_updates"); }
$db->query("CREATE TABLE IF NOT EXISTS `payments_updates` (
`id` INT(11) NOT NULL,
`login` VARCHAR(255) NOT NULL,
`full_name` VARCHAR(255),
`ip` VARCHAR(255),
`balance` DOUBLE,
`credit` DOUBLE,
`user_change_date` INT(11),
`payment_date` INT(11),
`tariff_id` INT(11),
`unlimited` INT(11),
`is_blocked` INT(11),
`is_deleted` INT(11),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;") or die $Mysql::db_errstr;
}
sub max {
my $maximum = shift @$_;
my $x;
foreach $x (@_) {
$maximum=$x if ($x > $maximum) ;
}
return $maximum;
}Код: Выделить всё
<?php
echo "<head>";
echo "<style><!--\n";
echo "body {font-family:verdana;font-size:10pt;background-color:ffffff;}\n";
echo "td {font-family:verdana;font-size:10px}\n";
echo "a {color: blue;text-decoration: none;}\n";
echo "a:hover {color: red;text-decoration: underline;}\n";
echo "th {font-size: 12px;}\n";
echo ".copy {font-size: 10px;color:cccccc;}\n";
echo "--></style>\n";
echo "<meta http-equiv=\"Refresh\" CONTENT=\"300\">\n";
echo "<meta http-equiv=\"Pragma\" CONTENT=\"no-cache\">\n";
echo "<meta http-equiv=\"Cache-Control\" content=\"no-cache\">\n";
echo "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=windows-1251\">";
$interval=3; $config = file('/netup/utm5/utm5.cfg'); foreach($config as $line_num => $line){if(preg_match("/^([^#].+?)=(.*)$/", $line, $param)){$$param[1]=$param[2];}}
mysql_connect($databese_host, $database_login, $database_password) or die("Could not connect : " . mysql_error());
mysql_select_db('UTM5') or die("Could not select database"); $ip=$_SERVER['REMOTE_ADDR'];
$query = "SET NAMES cp1251"; $result = mysql_query($query) or die("Query failed : " . mysql_error());
echo "<div align=center><table width=\"100%\" cellspacing=\"3\" cellpadding=\"3\"><tr bgcolor=#CCCCCC>
<th><b>логин</th><th>полное имя</th><th>дата последнего платежа</th><th>ip адрес</th><th>баланс</th><th>кредит</th><th>безлимит</th></tr>";
$sql=@mysql_query("SELECT login, full_name, ip, truncate(balance,0) AS balance, truncate(credit,0) AS credit, from_unixtime(payment_date) AS payment_date, unlimited, tariff_id FROM payments_updates
WHERE is_deleted = 0 GROUP BY id HAVING MAX(payment_date) < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL $interval MONTH)) OR payment_date IS NULL ORDER BY payment_date ASC");
while ($ro=@mysql_fetch_array($sql)){ if($ro[tariff_id] == 1 or $ro[tariff_id] == 17 or $ro[ip] == ''){ continue; } $i++;
echo "<tr><td>$ro[login]</td><td>$ro[full_name]</td><td>$ro[payment_date]</td><td>$ro[ip]</td><td align=right>$ro[balance]</td><td align=right>$ro[credit]</td><td align=right>$ro[unlimited]</td></tr>";
}
echo "<tr bgcolor=#CCCCCC>
<th><b>логин</th><th>полное имя</th><th>дата последнего платежа</th><th>ip адрес</th><th>баланс</th><th>кредит</th><th>безлимит</th></tr>";
echo "</table></div>";
echo "<title>Должники ($interval месяца) - $i чел.</title>";
echo "</head>";
?>P.S. Код, конечно, далёк от совершенства
, но может кому пригодится...P.P.S. Также интересно, как эта задача решается у других операторов. Наверняка есть более красивые решения.
