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

cd /usr/ports/databases/p5-DBI && make install clean
cd /usr/ports/databases/p5-DBD-mysql && make install clean
cd /usr/ports/security/p5-Crypt-Rijndael && make install clean
cd /usr/ports/converters/p5-JSON && make install clean
cd /usr/ports/converters/p5-JSON-XS && make install clean

svn checkout путь = git clone путь

svn up          = git pull

svn commit    = git commit -a -m "desc" && git push -u origin master

svn status     = git status

svn log          = git log

 /usr/bin/perl /usr/local/nodeny/nokernel.pl -m=cap -d &

#!/bin/sh -
f='/sbin/ipfw'

ifOut='tun0'

ifVia=''
ifRecv=''
tmp_or=''
for i in $ifOut
  do
    ifVia="${ifVia}${tmp_or}via $i"
    ifRecv="${ifRecv}${tmp_or}recv $i"
    tmp_or=' or '
  done

${f} -f flush

# dns, www.liqpay.com, liqpay.com
${f} table 100 add 8.8.8.8
${f} table 100 add 50.16.196.80
${f} table 100 add 184.73.55.73

# mysql slave server cs.csmoney.net
${f} table 101 add 50.17.182.68

${f} add 50 allow tcp from any to me 22
${f} add 51 allow tcp from me 22 to any

${f} add 110 allow ip from any to any via lo0
${f} add 120 skipto 1000 ip from me to any
${f} add 130 deny icmp from any to any in icmptype 5,9,13,14,15,16,17
${f} add 160 skipto 2000 ip from any to me

${f} add 200 skipto 500 ip from any to any { $ifVia }

${f} add 300 skipto 4500 ip from any to any in

${f} add 400 skipto 450 ip from any to any { $ifRecv }
${f} add 420 divert 1 ip from any to any
${f} add 450 divert 2 ip from any to any
${f} add 490 allow ip from any to any

${f} add 500 skipto 32500 ip from any to any in
${f} add 510 divert 1 ip from any to any
${f} add 540 allow ip from any to any


${f} add 1000 allow udp from any 53,7723 to any
${f} add 1010 allow tcp from any to any setup keep-state
${f} add 1020 allow udp from any to any keep-state
${f} add 1100 allow ip from any to any

${f} add 2000 check-state
${f} add 2010 allow icmp from any to any
${f} add 2020 allow tcp from any to any 22,80,443,5006
${f} add 2030 allow tcp from "table(101)" to any 3306
${f} add 2050 deny ip from any to any { $ifVia }
${f} add 2060 allow udp from any to any 53,7723

${f} add 2100 deny ip from any to any

${f} add 4500 allow ip from any to "table(100)"
${f} add 32490 deny ip from any to any

${f} add 32500 allow ip from "table(100)" to any

DROP TRIGGER tr_users_2;
DROP TRIGGER tr_users_3;

DROP TRIGGER IF EXISTS `tr_users_2`;
DROP TRIGGER IF EXISTS `tr_users_3`;

INSERT admin SET login='admin', name='', post='', privil=',1,3,2,', usr_grps='', passwd=AES_ENCRYPT('33','BIGint');

Alias /htdocs/ "/usr/local/www/apache22/htdocs/"
    <Directory "/usr/local/www/apache22/htdocs/">
        Options none
        AllowOverride Limit
        Order Deny,Allow
        Deny from all
        Allow from all
    </Directory>

Ошибка

SQL-запрос:  

DELIMITER $$ CREATE TRIGGER tr_users_2 BEFORE UPDATE ON users FOR EACH ROW IF( SELECT id
FROM admin
WHERE login = NEW.name
LIMIT 1 ) 
THEN SET NEW.name = OLD.name;

END IF ;

CREATE TRIGGER tr_users_3 BEFORE INSERT ON users
FOR EACH
ROW IF( 
SELECT id
FROM admin
WHERE login = NEW.name
LIMIT 1 ) 
THEN SET NEW.name = NULL ;

END IF ;

$$ 

Ответ MySQL:  
 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TRIGGER tr_users_3 BEFORE INSERT ON users  
     FOR EACH ROW
     IF( SELE' at line 5

DELIMITER $$

#!/usr/bin/perl
# ------------------- NoDeny ------------------
# Copyright (с) Volik Stanislav, 2008..2012
# Read license http://nodeny.com.ua/license.txt
# ---------------------------------------------
use strict;
use web::Pay;

 $cfg::mess_max_msgs ||= 10;
 $cfg::mess_max_chars ||= 1500;

sub go
{
 my $urlm = @_;
 my($url,$usr) = @_;
 my $uid = $usr->{id};
 my $sql = ["SELECT * FROM pays WHERE mid=? AND category IN(480,481,488,491,492) ORDER BY time DESC", $uid];
 my($sql, $page_buttons, $rows, $db) = Show_navigate_list($sql, ses::input_int('start'), 5, $url);
 $rows>0 or return;
#______________
    my $tbl2 = tbl->new( -class=>'pretty td_wide' );
    $tbl2->add('', 'll', [ v::input_ta( 'mesg', '', 100, 5 ) ], );
    $tbl2->add('', 'C', [ v::submit('send') ]);
    $tbl2->ins('big', 'C', 'Message to admins');
    my $value=ses::input('mesg');
    if( length $value>$cfg::mess_max_chars )
    {
	ErrorMess("mnogo simvolov");	
    }
    if( length $value>1 )
    {
	my $rows = Db->do("INSERT INTO pays SET mid=?, comment=?, category=? ", $uid, $value, 491);
	$rows<1 && $url->redirect( -made=>$lang::err_try_again, -error=>1 );
    }
    

#_____________
 my $tbl = tbl->new( -class=>'td_tall td_wide' );
 $tbl->add('head', 'rrcl', '', '');
 while( my %p = $db->line )
 {
    my $client = $p{mid}? url->a($p{mid}, a=>'ajUserInfo', uid=>$p{mid}, -ajax=>1) : '';
    my $decode = Pay::decode(\%p);
    my $creator_id = $p{creator_id};
    my $creator = $p{creator} eq 'admin'? 'Admin: '.Adm->get($creator_id)->login : 
                  $p{creator} eq 'user' ? 'User: you' :
                  $p{creator} eq 'other'? 'Other' :
                  $p{creator};

    $tbl->add('*','rrll', [ the_time($p{time}) ], [ $creator ], [ $decode->{for_adm} ],
        [ !!Adm->id && url->a('info', a=>'ajPayInfo', id=>$p{id}, -ajax=>1) ]);
 }


    my %p = Db->line("SELECT COUNT(*) AS n FROM pays WHERE mid=? AND category IN(480,481,488,491,492) AND time>(UNIX_TIMESTAMP()-3600*24)", $uid);
#    %p && $p{n} >= $cfg::card_max_msgs && Error($lang::sCards_many_errs);
    if(%p && $p{n} <= $cfg::mess_max_msgs)
    {
    Show Center($url->form(act=>'save', $tbl2->show), $page_buttons.$tbl->show.$page_buttons );
    }
    else
    {
    Show Center( "ffff", $page_buttons.$tbl->show.$page_buttons );
    }
} 

1;

Pay_to_DB(uid=>.., category=>..., reason=>..)

#!/usr/bin/perl
# ------------------- NoDeny ------------------
# Copyright (с) Volik Stanislav, 2008..2012
# Created by Redmen
# isq: 355770128 
# Read license http://nodeny.com.ua/license.txt
# ---------------------------------------------
use strict;
use web::Pay;

 $cfg::mess_max_mesgs ||= 10;
 $cfg::mess_max_chars ||= 1000;

sub go
{
 my($url,$usr) = @_;
 Doc->template('top_block')->{title} .= '. Mess to admin';
 my $uid = $usr->{id};
#_______forma vvoda mesg_______
    my $tbl2 = tbl->new( -class=>'pretty td_wide' );
    $tbl2->add('', 'll', [ v::input_ta( 'mesg', '', 100, 5 ) ], );
    $tbl2->add('', 'C', [ v::submit('otpravit mesg adminy') ]);
#    $tbl2->ins('big', 'C', 'Message to admins');
    my $value=ses::input('mesg');
    $value =~ s/\n/<br>/g;
    my %p = Db->line("SELECT COUNT(*) AS n FROM pays WHERE mid=? AND category IN(480,481,488,491,492) AND time>(UNIX_TIMESTAMP()-3600*24)", $uid);
    if(%p && $p{n} >= $cfg::mess_max_mesgs)
    {
    	ErrorMess("limit mesg > 3 ");	
	$value='';
    }
    if( length $value>$cfg::mess_max_chars )
    {
	ErrorMess("mnogo simvolov");	
	$value='';
    }
    if( length $value>1 )
    {
	my $rows = Pay_to_DB(uid=>$uid, category=>491, reason=>$value);
        $url->redirect( -made=>'Сообщение отправлено администрации' );
	#Db->do("INSERT INTO pays SET mid=?, comment=?, category=? ", $uid, $value, 491);
	$rows<1 && $url->redirect( -made=>'Сообщение отправлено администрации', -error=>1  );
    }
#_______END forma mesg_______


#_______vivod tbl mesg_______
 my $sql = ["SELECT * FROM pays WHERE mid=? AND category IN(480,481,488,491,492) ORDER BY time DESC", $uid];
 my($sql, $page_buttons, $rows, $db) = Show_navigate_list($sql, ses::input_int('start'), 5, $url);
 my $tbl = tbl->new( -class=>'td_tall td_wide' );
 $tbl->add('head', 'rrcl', '', '');
 while( my %p = $db->line )
 {
    my $client = $p{mid}? url->a($p{mid}, a=>'ajUserInfo', uid=>$p{mid}, -ajax=>1) : '';
    my $decode = Pay::decode(\%p);
    my $creator_id = $p{creator_id};
    my $creator = $p{creator} eq 'admin'? 'Admin: '.Adm->get($creator_id)->login : 
                  $p{creator} eq 'user' ? 'User: you' :
                  $p{creator} eq 'other'? 'Other' :
                  $p{creator};

    $tbl->add('*','rrll', [ the_time($p{time}) ], [ $creator ], [ $decode->{for_adm} ],
        [ !!Adm->id && url->a('info', a=>'ajPayInfo', id=>$p{id}, -ajax=>1) ]);
 }
#_______END tbl mesg_________

    Show Center($url->form(act=>'save', $tbl2->show), $page_buttons.$tbl->show.$page_buttons );
} 

1;

Table 'bill.fullusers' doesn't exist 

{
  'sql' => 'SELECT SQL_CALC_FOUND_ROWS u.* FROM fullusers u
 WHERE u.grp IN(13,12,9,4) ORDER BY u.id DESC LIMIT 0,10',
  'param' => []
};
 
 SELECT SQL_CALC_FOUND_ROWS u.* FROM fullusers u
 WHERE u.grp IN(13,12,9,4) ORDER BY u.id DESC LIMIT 0,10
Строк: -1. Время выполнения sql: 0.0010 сек

CREATE ALGORITHM=MERGE VIEW fullusers AS
    SELECT u.*, t.*,
    ((in1+in2+in3+in4+out1+out2+out3+out4)/1000000) AS traf,
    ((out1+out2+out3+out4)/1000000) AS traf_out,
    ((in1+in2+in3+in4)/1000000) AS traf_in
    FROM users u LEFT JOIN users_trf t ON u.id=t.uid;

Table 'bill.v_ips' doesn't exist 

{
  'sql' => 'SELECT MAX(last) AS max_last FROM v_ips WHERE uid=? AND auth>0',
  'param' => [
    1378
  ]
};
 
 SELECT MAX(last) AS max_last FROM v_ips WHERE uid='1378' AND auth>0
Строк: -1. Время выполнения sql: 0.0012 сек

CREATE ALGORITHM=MERGE VIEW v_ips AS
    SELECT
        IF(a.start IS NULL,0,1) AS auth, i.uid, INET_NTOA(i.ip) AS ip, i.ip AS ipn,
        i.type, i.`release`, (a.last -a.start) AS tm_auth, a.start, a.last, a.properties 
    FROM ip_pool i
    LEFT JOIN auth_now a ON INET_NTOA(i.ip) = a.ip
    WHERE i.uid<>0;

Ошибка:
временная ошибка

Если ошибка устойчивая - к администратору (см. Debug)

Table 'bill.plans2' doesn't exist 

{
  'sql' => 'UPDATE users SET balance=balance+(?) WHERE id=? LIMIT 1',
  'param' => [
    0,
    1396
  ]
};
 
 UPDATE users SET balance=balance+('0') WHERE id='1396' LIMIT 1
Строк: -1. Время выполнения sql: 0.0015 сек

DROP TABLE `plans2`;

     perl install.pl -h
    -a=login -p=pass : создание суперадмина с логином login и паролем pass
    -m : создание пустого конфига

¬—AВЪ?­e2ф2Пэ8Y

ALTER TABLE users MODIFY COLUMN passwd BLOB NOT NULL;

mysql> select count(*) from del2;
+----------+
| count(*) |
+----------+
|   292040 |
+----------+
1 row in set (0.08 sec)

mysql> select count(*),category from del2 group by category;
+----------+----------+
| count(*) | category |
+----------+----------+
|     9520 |        1 |
|     4120 |        2 |
|      200 |        3 |
|    22360 |       20 |
|     3160 |       50 |
|    40480 |       99 |
|    89760 |      100 |
...
...
|    37080 |      423 |
|    34240 |      444 |
+----------+----------+
30 rows in set (0.10 sec)

mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT p.*, u.fio, u.name FROM pays p LEFT JOIN users u ON p.mid = u.id WHERE 1 AND p.time >= '1347138000' AND p.category<>3 ORDER BY time DESC LIMIT 0,15;
 
15 rows in set (0.00 sec)

0.075231

159	stat.pl
1049	calls.pm::Require_web_mod
1043	calls.pm::Require_mod
1043	calls.pm
48	start_admin.pl

Неизвестная команда `yamap`