billing.pm

Astercon2 开源软交换 2.2.0

package astercon::database::admin;

use Carp qw(carp croak);
use strict;
use Time::HiRes qw(gettimeofday);

#------------------------------------
# for extensions tables
#------------------------------------
sub exten_exists_accountcode
{
my	$self = shift;
my	$Accountcode = shift;
my	$Expiry = shift;
my	$addon_where_sql;

	$addon_where_sql = " AND Billing_expiry >= ".$self->{dbh}->quote($Expiry) if ($Expiry);
my	$sth = $self->{dbh}->prepare("SELECT * FROM Billing_account WHERE Accountcode = ".
		$self->{dbh}->quote($Accountcode)." $addon_where_sql");
	$sth->execute or croak($self->{dbh}->errstr);
my	$row = $sth->fetchrow_hashref();
	$sth->finish;
	return(2,$row) if ($row && $row->{'Accountcode'} ne '');

my	$sth = $self->{dbh}->prepare("SELECT * FROM Billing_freecode WHERE Accountcode = ".
		$self->{dbh}->quote($Accountcode));
	$sth->execute or croak($self->{dbh}->errstr);
my	$row = $sth->fetchrow_hashref();
	$sth->finish;
	return(1,$row) if ($row && $row->{'Accountcode'} ne '');

# 0 all no exists 1 freecode 2 exists in account table
return(0,$row);
}

sub exten_add_account
{
my	$self = shift;
my	%info = @_;
my	($sql);

	$info{'Billing_total'} = 0 if ($info{'Billing_total'} eq '');
	$info{'Billing_free'} = 0 if ($info{'Billing_free'} eq '');
	$info{'Billing_credit'} = 0 if ($info{'Billing_credit'} eq '');

	#string of sql language
	$sql = "INSERT INTO Billing_account(Accountcode,Username,Callerid,Secret,Credate,Detail,Status,Remark,";
	$sql .= "SendCallerid,Sipdevice,Iaxdevice,Billing_total,Billing_free,Billing_credit,Billing_expiry,";
	$sql .= "Billing_rate_id) VALUES(";
	$sql .= $self->{dbh}->quote($info{'Accountcode'}).','.$self->{dbh}->quote($info{'Username'}).','.
			$self->{dbh}->quote($info{'Callerid'}).','.$self->{dbh}->quote($info{'Secret'}).','.
			'now(),'.$self->{dbh}->quote($info{'Detail'}).','.
			'1,'.$self->{dbh}->quote($info{'Remark'}).','.
			$self->{dbh}->quote($info{'SendCallerid'}).','.$self->{dbh}->quote($info{'Sipdevice'}).','.
			$self->{dbh}->quote($info{'Iaxdevice'}).','.$self->{dbh}->quote($info{'Billing_total'}).','.
			$self->{dbh}->quote($info{'Billing_free'}).','.$self->{dbh}->quote($info{'Billing_credit'}).','.
			$self->{dbh}->quote($info{'Billing_expiry'}).','.$self->{dbh}->quote($info{'Billing_rate_id'}).')';

	$self->{dbh}->do($sql) or die $self->{dbh}->errstr;

return();
}

sub exten_edit_account
{
my	$self = shift;
my	%info = @_;
my	($sql);

	#string of sql language
	$sql = "UPDATE Billing_account SET ";
	$sql .= "Username = ".$self->{dbh}->quote($info{'Username'})."," if ($info{'Username'} ne '');
	$sql .= "Callerid = ".$self->{dbh}->quote($info{'Callerid'}).",";# if ($info{'Callerid'} ne '');
	$sql .= "Secret = ".$self->{dbh}->quote($info{'Secret'})."," if ($info{'Secret'} ne '');
	$sql .= "Detail = ".$self->{dbh}->quote($info{'Detail'}).",";# if ($info{'Detail'} ne '');
	$sql .= "Status = ".$self->{dbh}->quote($info{'Status'})."," if ($info{'Status'} ne '');
	$sql .= "Remark = ".$self->{dbh}->quote($info{'Remark'}).",";# if ($info{'Remark'} ne '');
	$sql .= "SendCallerid = ".$self->{dbh}->quote($info{'SendCallerid'}).",";# if ($info{'SendCallerid'} ne '');
	$sql .= "Sipdevice = ".$self->{dbh}->quote($info{'Sipdevice'})."," if ($info{'Sipdevice'} ne '');
	$sql .= "Iaxdevice = ".$self->{dbh}->quote($info{'Iaxdevice'})."," if ($info{'Iaxdevice'} ne '');
	$sql .= "Billing_credit = ".$self->{dbh}->quote($info{'Billing_credit'})."," if ($info{'Billing_credit'} ne '');
	$sql .= "Billing_rate_id = ".$self->{dbh}->quote($info{'Billing_rate_id'})."," if ($info{'Billing_rate_id'} ne '');
	$sql =~ s/\,$//;
	$sql .= " WHERE Accountcode = ".$self->{dbh}->quote($info{'Accountcode'});

	$self->{dbh}->do($sql) or die $self->{dbh}->errstr;

return();
}

sub exten_fetch_freecode
{
my	$self = shift;
my	$max=40;
my	(@returndata,@data);

my	$sth = $self->{dbh}->prepare("SELECT * FROM Billing_freecode ORDER BY rand() LIMIT $max") or die $self->{dbh}->errstr;
	$sth->execute or croak($self->{dbh}->errstr);
	while (my	$row = $sth->fetchrow_hashref()) {
		push(@data,$row->{'Accountcode'});
		if ($#data == 9) {
			push(@returndata,[@data]);
			$#data = -1;
		}
	}
	push(@returndata,[@data]) if ($#returndata <= 2 && $#data >= 0);
	$sth->finish;

return(\@returndata);
}

sub exten_list
{
my	$self = shift;
#my	%args = @_;
#
#	if ($args{fetch} eq 'all') {
#
#	my	@returndata;
#	my	$sth = $self->{dbh}->prepare("SELECT * FROM Billing_account ORDER BY Credate DESC") or die $self->{dbh}->errstr;
#		$sth->execute or croak($self->{dbh}->errstr);
#		while (my $row = $sth->fetchrow_hashref()) {
#			push(@returndata,$row);
#		}
#		$sth->finish;
#
#	return(\@returndata);
#
#	} else {
#
#	my	$sth = $self->{dbh}->prepare("SELECT COUNT(*) FROM Billing_account") or die $self->{dbh}->errstr;
#		$sth->execute or croak($self->{dbh}->errstr);
#	my	$row = $sth->fetchrow_arrayref();
#		$sth->finish;
#
#	return($row->[0]);
#
#	}

my	%info = @_;
#limit=>1
#order=>xxxx
#only_count=>yes
my	(@returndata,$where_sql,$order_sql,$sth);

	if ($info{order} ne '') {	$order_sql = 'ORDER BY '.$info{order}.' DESC';	}
	if ($info{limit} ne '') {		$order_sql .= ' LIMIT '.$info{limit};	}

	if ($info{only_count} ne '') {	$sth = $self->{dbh}->prepare("SELECT COUNT(*) FROM Billing_account $where_sql");}
	else {	$sth = $self->{dbh}->prepare("SELECT * FROM Billing_account $where_sql $order_sql");	}
	$sth->execute or croak($self->{dbh}->errstr);
	while (my $row = $sth->fetchrow_hashref()) {
		push(@returndata,$row);
	}
	$sth->finish;

return(\@returndata);
}


sub exten_remove
{
my	$self = shift;
my	$accountcode = shift;
	$self->{dbh}->do("DELETE FROM Billing_account WHERE Accountcode = ".$self->{dbh}->quote($accountcode)) 
		or die $self->{dbh}->errstr;
return();
}

sub exten_add_freecode
{
my	$self = shift;
my	%args = @_;

my	$sth = $self->{dbh}->prepare("SELECT COUNT(*) FROM Billing_freecode WHERE Accountcode = ".
		$self->{dbh}->quote($args{'accountcode'})) or die $self->{dbh}->errstr;
	$sth->execute or croak($self->{dbh}->errstr);
my	$row = $sth->fetchrow_arrayref();
	$sth->finish;

	$self->{dbh}->do("INSERT INTO Billing_freecode(Accountcode) VALUES(".$self->{dbh}->quote($args{'accountcode'}).
		")") or die $self->{dbh}->errstr if ($row->[0] == 0);
return();
}

sub exten_get_freecode
{
my	$self = shift;
my	%args = @_;

	if ($args{'erase'} eq 'all') {
		$self->{dbh}->do("DELETE FROM Billing_freecode") or die $self->{dbh}->errstr;
	} else {
		$self->{dbh}->do("DELETE FROM Billing_freecode WHERE Accountcode = ".
			$self->{dbh}->quote($args{'accountcode'})) or die $self->{dbh}->errstr;
	}

return();
}

sub exten_count_freecode
{
my	$self = shift;
my	%args = @_;

	if ($args{fetch} eq 'all') {

	my	@returndata;
	my	$sth = $self->{dbh}->prepare("SELECT * FROM Billing_freecode") or die $self->{dbh}->errstr;
		$sth->execute or croak($self->{dbh}->errstr);
		while (my $row = $sth->fetchrow_hashref()) {
			push(@returndata,$row);
		}
		$sth->finish;

	return(\@returndata);

	} else {

	my	$sth = $self->{dbh}->prepare("SELECT COUNT(*) FROM Billing_freecode") or die $self->{dbh}->errstr;
		$sth->execute or croak($self->{dbh}->errstr);
	my	$row = $sth->fetchrow_arrayref();
		$sth->finish;

	return($row->[0]);

	}
}

sub billing_change_logger
{
my	$self = shift;
my	%info = @_;
my	($sql);

	#string of sql language
	$sql = "INSERT INTO Billing_logger(Credate,Accountcode,Billing_card_amount,Billing_card_days,Type,adminid) VALUES(";
	$sql .= 'now(),'.$self->{dbh}->quote($info{'Accountcode'}).','.$self->{dbh}->quote($info{'Billing_card_amount'}).
			','.$self->{dbh}->quote($info{'Billing_card_days'}).','.$self->{dbh}->quote($info{'Type'}).','.
			$self->{dbh}->quote($info{'adminid'}).')';
	$self->{dbh}->do($sql) or die $self->{dbh}->errstr;

return();
}

sub billing_list_logger
{
my	$self = shift;
my	%info = @_;
	return if ($info{Accountcode} eq '');
my	@returndata;

my	$sth = $self->{dbh}->prepare("SELECT * FROM Billing_logger WHERE Accountcode = ".
			$self->{dbh}->quote($info{Accountcode}).' ORDER BY Credate DESC');
	$sth->execute or croak($self->{dbh}->errstr);
	while (my $row = $sth->fetchrow_hashref()) {
		push(@returndata,$row);
	}
	$sth->finish;

return(\@returndata);
}

sub billing_charge_account
{
my	$self = shift;
my	%info = @_;
	return if ($info{Accountcode} eq '');

	if ($info{Billing_free} ne '') {
	my	$sql = "UPDATE Billing_account SET ";
		if ($info{'Billing_free'} !~ /^\-/) {
			$sql .= "Billing_total = Billing_total +".$self->{dbh}->quote($info{'Billing_free'}).",";
		}
		$sql .= "Billing_free = Billing_free + ".$self->{dbh}->quote($info{'Billing_free'});
		$sql .= " WHERE Accountcode = ".$self->{dbh}->quote($info{'Accountcode'});
		$self->{dbh}->do($sql) or die $self->{dbh}->errstr;
	} else {
	my	$sql = "UPDATE Billing_account SET ";
		$sql .= "Billing_expiry = ".$self->{dbh}->quote($info{'Billing_expiry'});
		$sql .= " WHERE Accountcode = ".$self->{dbh}->quote($info{'Accountcode'});
		$self->{dbh}->do($sql) or die $self->{dbh}->errstr;
	}

return();
}

sub billing_add_rate
{
my	$self = shift;
my	%args = @_;

	#add group and retrun id
	if (defined $args{'Id'}) {
		$self->{dbh}->do("INSERT INTO Billing_rate(Id,Title,Remark,Lcr_priority) VALUES(".
						$self->{dbh}->quote($args{'Id'}).','.$self->{dbh}->quote($args{'title'}).','.
						$self->{dbh}->quote($args{'remark'}).','.$self->{dbh}->quote($args{'lcr_priority'}).')') 
						or die $self->{dbh}->errstr;
	} else {
		$self->{dbh}->do("INSERT INTO Billing_rate(Title,Remark,Lcr_priority) VALUES(".
						$self->{dbh}->quote($args{'title'}).','.$self->{dbh}->quote($args{'remark'}).','.
						$self->{dbh}->quote($args{'lcr_priority'}).')') or die $self->{dbh}->errstr;
	}

return();
}

sub billing_list_rate
{
my	$self = shift;
my	@returndata;

my	$sth = $self->{dbh}->prepare("SELECT * FROM Billing_rate ORDER BY Id DESC");
	$sth->execute or croak($self->{dbh}->errstr);
	while (my $row = $sth->fetchrow_hashref()) {
		push(@returndata,$row);
	}
	$sth->finish;

return(\@returndata);
}

sub billing_get_rate
{
my	$self = shift;
my	$rate_id = shift;

my	$sth = $self->{dbh}->prepare("SELECT * FROM Billing_rate WHERE Id = ".$self->{dbh}->quote($rate_id));
	$sth->execute or croak($self->{dbh}->errstr);
my	$row = $sth->fetchrow_hashref();
	$sth->finish;

return($row);
}

sub billing_change_rate
{
my	$self = shift;
my	%info = @_;
my	($sql);
	return if ($info{'Title'} eq '');

	#string of sql language
	$sql = "UPDATE Billing_rate SET ";
	$sql .= "Title = ".$self->{dbh}->quote($info{'Title'}).",";
	$sql .= "Lcr_priority = ".$self->{dbh}->quote($info{'Lcr_priority'}).",";
	$sql .= "Remark = ".$self->{dbh}->quote($info{'Remark'})."," if ($info{'Remark'} ne '');
	$sql =~ s/\,$//;
	$sql .= " WHERE Id = ".$self->{dbh}->quote($info{'Id'});

	$self->{dbh}->do($sql) or die $self->{dbh}->errstr;

return();
}

sub billing_remove_rate
{
my	$self = shift;
my	$rate_id = shift;

	#妫