Биллинговая система Nodeny

#уже не надо require 'nHack.pl' unless $PR{1}; т.к. скрипт не cgi-bin
use strict;
use vars qw( &Show &ToTop &ErrTop &ToLeft $Db $Url $DOC %F %A $TblGood );

$A{pr}{2} or &Error('Вам не разрешен доступ.');
$DOC{align}='center'; # выравнивание контента по центру горизонтали

my $Fact=$F{act} || 'tarifstat';

ToTop &div( 'nav',
  $Url->a( 'Статистика по тарифам', 'act'=>'tarifstat').' '.$Url->a( 'Статистика по групам', 'act'=>'grpstat' )
);
......
 Show $tbl->show;

# в обрабочик поля `баланс` вставляем свой обработчик
main::plugin_init(
    set_data_alias => {
                        'form balance'      => \&form_balance,
    },
);

sub form_balance
{
 my $it = shift;
 # получим данные всех полей, у которых алиас начинается с traf_
 my $traf = $it->get(
    alias => 'traf_%',
 );
 foreach( @$traf )
 {
    $it->{descr} .= $_->{alias}.': '.$_->{val};
    $it->{val} += $_->{val} * 0.01; # трафик идет по 1 коп за Мб
 }
}

Количество выводимых записей на страницу при просмотре списка клиентов

Основной запрос:
SELECT SQL_CALC_FOUND_ROWS id FROM users WHERE id IN (SELECT DISTINCT parent_id AS id FROM dopvalues WHERE revision IN (SELECT rev FROM rev_users WHERE template_num=2) AND dopfield_id=18 AND field_value LIKE '59') LIMIT 0,60
Время выполнения sql: 7.047915 сек
Всего строк: 361

Дом на улице Фрунзе(М) :
40 44 34 38 32 50 36 60 46 48 52 59 58 39 81

Название поля:  	Квартира	Телефон	Улица	Комментарий	Подъезд	Этаж	Блок	Дом

"Белый" ИП	 
MAC адрес клиента	00:11:D8:2F:67:8D
VLAN клиента	 
Комментарий

my $rows = sql_do($dbh, 'DELETE FROM uzers');
$rows>0 && Error("Удалены все клиенты!!");
Error("На самом деле не удалены т.к. имя таблицы задано неверно");

$year = int $F{year};
$year = 0 if ($year eq '');

$day = '0'.$day if ($day<10);
$month = '0'.$month if ($month<10);

alive!='good' AND alive!='bad' AND alive!='move' AND alive!='stock'

alive NOT IN ('good','bad','move','stock')

$year ne 0

#!/bin/bash
mysql -sse "INSERT INTO yakumodzo (number,sign,message) VALUES ($1,$2,$3)" -h77.120.116.10 -uyakumodzo -pтутбылпароль users

sub go
{
    my($Url, $usr) = @_;
    my %p = Db->line("SELECT cash, comment FROM pays WHERE mid=? AND category=1 ORDER BY id DESC LIMIT 1", $usr->{id});
    %p or Error($lang::s_soft_error);

    $usr->{last_pay} = { summa=>$p{cash}, comment=>$p{comment}, date=>the_date($ses::t) };

    my $html = tmpl('cash_order', %$usr);

    print "Content-type: text/html\n\n";
    print $html;
    exit;
}

 ALTER TABLE `cards` CHANGE `stime` `tm_create` INT( 11 ) UNSIGNED NOT NULL DEFAULT '0';
 ALTER TABLE `cards` CHANGE `etime` `tm_end` INT( 11 ) UNSIGNED NOT NULL DEFAULT '0';
 ALTER TABLE `cards` CHANGE `atime` `tm_activate` INT( 11 ) UNSIGNED NOT NULL DEFAULT '0';
 ALTER TABLE `cards` CHANGE `admin` `adm_create` MEDIUMINT( 8 ) UNSIGNED NOT NULL DEFAULT '0';
 ALTER TABLE `cards` ADD `alive2` ENUM( 'good', 'bad', 'stock', 'activated' ) NOT NULL DEFAULT 'good' AFTER `adm_create`;
 ALTER TABLE `cards` ADD `uid_activate` INT UNSIGNED NOT NULL DEFAULT '0' AFTER `alive2`;
 UPDATE cards SET alive2=alive WHERE alive IN('good','bad','stock');
 UPDATE cards SET alive2='activated', uid_activate=alive WHERE alive>0;
 ALTER TABLE `cards` DROP `alive`;
 ALTER TABLE `cards` CHANGE `alive2` `alive` ENUM( 'good', 'bad', 'stock', 'activated' ) NOT NULL DEFAULT 'good';
 ALTER TABLE `cards` DROP `rand_id`;
 ALTER TABLE `cards` DROP `admin_sell`;
 ALTER TABLE `cards` DROP `time_sell`;
 ALTER TABLE `cards` DROP `id_sell`;
 ALTER TABLE `cards` CHANGE `r` `adm_owner` MEDIUMINT(9) UNSIGNED NOT NULL DEFAULT '0';
 ALTER TABLE `cards` ADD `adm_move` MEDIUMINT UNSIGNED NOT NULL DEFAULT '0' AFTER `adm_owner`;
 

 make_config => {
        run => 1,
        period => 60,
        config => [
            'dhcp.tmpl',
        ]
    },

dhcp_server => {
    },

   elsif( $last{adm_owner} == Adm->id )
    {
        $actions .= $Url->a('п÷п╣я─п╣пЄп╟я┌я▄', %url, act=>'cards_move_step1');
       $actions .= $Url->a('  export', %url, act=>'cards_export_csv');
        foreach my $act(
            [ good  => 'п▓ я─п╟п╠п╬я┌я┐'],
            [ stock => 'п²п╟ я│п╨п╩п╟пЄ'],
            [ bad   => 'п▒п╩п╬п╨п╦я─п╬п╡п╟я┌я▄'],

  my %subs = (
    dontshowmess       => 1,    # я┐пЄп╟п╩п╦я┌я▄ я│п╬п╬п╠я┴п╣пҐп╦п╣ пЄп╩я▐ п╟пЄп╪п╦пҐп╟ п╬я┌ я│я┐п©п╣я─п╟пЄп╪п╦пҐп╟
    cards_move_step1   => 1,    # п╡я▀п╠п╬я─ п╟пЄп╪п╦пҐп╦я│я┌я─п╟я┌п╬я─п╟ пЄп╩я▐ п©п╣я─п╣пЄп╟я┤п╦
    cards_move_step2   => 1,    # пҐп╣п©п╬я│я─п╣пЄя│я┌п╡п╣пҐпҐп╟я▐ п©п╣я─п╣пЄп╟я┤п╟ п╨п╟я─я┌п╬я┤п╣п╨
    cards_move_accept  => 1,    # п©п╬пЄя┌п╡п╣я─пІпЄп╣пҐп╦п╣ п©п╣я─п╣пЄп╟я┤п╦ п╨п╟я─я┌п╬я┤п╣п╨ п©я─п╦пҐп╦п╪п╟я▌я┴п╦п╪ п╟пЄп
    cards_change_alive => 1,    # я│п╪п╣пҐп╟ я│п╬я│я┌п╬я▐пҐп╦я▐ п╨п╟я─я┌п╬я┤п╣п╨ (пҐп╟ я│п╨п╩п╟пЄп╣/п╪п╬пІпҐп╬ п╟п╨я┌п╦п╡п╦
    del_ses_data       => 1,
    cards_export_csv   => 1,
 );

sub cards_export_csv
{
my $dbh = Db->dbh;
my $p;
my $cards = cards_move_preload();
my($start, $end) = ($cards->{start}, $cards->{end});
my $body='';
 #Запрашиваем идентификатор, активационный код, стоимость карточки
my $sth=$dbh->prepare("SELECT `cid`, `cod`,  `money`  FROM `cards` WHERE cid>=$start AND cid<=$end");
$sth -> execute;
 while ($p=$sth->fetchrow_hashref)
  {
   #Закомментировать строку ниже, если нужно деньги привести к западному типу разделителя целой и дробной части
 $p->{money} =~ tr/./,/;
   #Генерируем ряды CSV для последующего экспорта например в MS Access
   $body.="\"$p->{cid}\";\"$p->{cod}\";\"$p->{money}\" \n";
  }
  #Выводим данные в браузер на сохранение
my $OUT="Content-Type: application/octet-stream\n".
  "Content-Disposition: attachment; filename=nocards.txt\n\n".
  "$body";
 print $OUT;
 exit;
}

 Db->do("CALL set_auth(?,?)", $usr->{ip}, ''

 Db->do("CALL set_auth(?,?)", $usr->{ip}, 'mod=dhcp'

mysql> help create trigger;
Name: 'CREATE TRIGGER'
Description:
Syntax:
CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_body

This statement creates a new trigger. A trigger is a named database
object that is associated with a table, and that activates when a
particular event occurs for the table. The trigger becomes associated
with the table named tbl_name, which must refer to a permanent table.
You cannot associate a trigger with a TEMPORARY table or a view. CREATE
TRIGGER was added in MySQL 5.0.2.

In MySQL 5.0 CREATE TRIGGER requires the SUPER privilege.

The DEFINER clause determines the security context to be used when
checking access privileges at trigger activation time. It was added in
MySQL 5.0.17. See later in this section for more information.

trigger_time is the trigger action time. It can be BEFORE or AFTER to
indicate that the trigger activates before or after each row to be
modified.

trigger_event indicates the kind of statement that activates the
trigger. The trigger_event can be one of the following:

o INSERT: The trigger is activated whenever a new row is inserted into
  the table; for example, through INSERT, LOAD DATA, and REPLACE
  statements.

o UPDATE: The trigger is activated whenever a row is modified; for
  example, through UPDATE statements.

o DELETE: The trigger is activated whenever a row is deleted from the
  table; for example, through DELETE and REPLACE statements. However,
  DROP TABLE and TRUNCATE TABLE statements on the table do not activate
  this trigger, because they do not use DELETE. See [HELP TRUNCATE
  TABLE].

URL: http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

use Net::ARP;
...
        my $mac = Net::ARP::arp_lookup('lo', $p{ipa} );
        $mac =~ s/://g;
        my $pmac;
        $pmac = "user=$mac;" if (length $mac == 12);
        my $opts = "mod=dhcp;$pmac";
        Db->do("CALL set_auth(?,?)", $p{ipa}, $opts);
...

SELECT INET_ATON('10.1.2.3');

,
    {
        name    => 'tm_end',
        title   => 'Конец услуги',
        type    => 12,
        search  => 1,
        s_mode  => 2,
    }

   # --- Услуги ---
    my @services = ();
    my @next_services = ();
    my @services_tm_end = ();
    my $db = Db->sql(
        "SELECT v.*, s.title AS next_title ".
        "FROM v_services v LEFT JOIN services s ON v.next_service_id=s.service_id ".
        "WHERE v.uid=?", $uid
    );
    while( my %serv = $db->line )
    {
        push @services, $serv{title};
        $serv{next_service_id} && push @next_services, $serv{next_title};
	push @services_tm_end, $serv{tm_end};
    }
    my $col_services = join '<br>', map{ v::filtr($_) } @services;
    my $col_next_services = join '<br>', map{ v::filtr($_) } @next_services;
    my $col_services_tm_end = join '<br>', map{ v::filtr($_) } the_date(@services_tm_end);

    _add_col 'l', 0, 'stm_end',     'Конец услуги', 	[$col_services_tm_end];

if( traf(30) > 100000 && traf_all() > 10000000 ) {
  set_speed(50000);
}

if( traf(10) < 12345 ) { set_speed(2048); return }
if( get_day() == 1 && get_hour() > 15 && traf(30) > 55555555 ) {
  set_speed(512);
  return;
}
set_speed(1024);

if( speed(5) < 256 ) { set_speed 1024 } else { set_speed 512 }