Lbilling-synchronization.pl
Z Freenetis Wiki
#!/usr/bin/perl
# ***************************************************************************
# * Copyright (C) 2005 by Dominik Strnad *
# * dominik.strnad@unhfree.net *
# * *
# * This program is free software; you can redistribute it and/or modify *
# * it under the terms of the GNU General Public License as published by *
# * the Free Software Foundation; either version 2 of the License, or *
# * (at your option) any later version. *
# * *
# * 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. *
# ***************************************************************************
use strict;
use DBI;
use Getopt::Std;
use Cz::Cstocs;
use Data::Dumper;
use warnings;
#use MIME::Lite::TT::HTML;
use vars qw( %opts $query $db $db_host $db_user $db_pass $file_lock $log_file $lbilling $dbh $czech_to_ascii $email_alert);
######### Configuration ########################################################
## alert on error by email?
$email_alert = 1;
## email configuration
$email_smtp = 'smtp.slfree.czf';
$email_address_from = 'no-reply@freenetis.org';
$email_address_to = 'monitoring@yourfreenet.net';
## database configuration
$db = "lbilling";
$db_host = "localhost";
$db_user = "lbilling";
$db_pass = "";
## setting to NFX
$nfx_username = "";
$nfx_password = "";
################################################################################
$file_lock = 1;
$log_file = ">>/var/log/lbilling.log";
unless ( $dbh = DBI->connect("DBI:mysql:database=$db;host=$db_host", $db_user, $db_pass, {RaiseError => 1}) ) {
&logging("Can't connect to database");
if ( $email_alert == 1 ){
&send_email("Can't connect to database");
}
die;
}
unless ( $dbh->do("SET NAMES 'utf8'") ) {
&logging("Can't set codepage");
if ( $email_alert == 1 ){
&send_email("Can't set codepage");
}
die;
}
$czech_to_ascii = new Cz::Cstocs 'utf8', 'ascii';
#opts:
# -s - synchronize - accounts and subscribers will be synchronized
# -p - payments - payments will be synchronized
&getopts( "sp", \%opts );
use SOAP::Lite +autodispatch =>
uri => "http://sip.nfx.czf/lBilling",
proxy => "https://$nfx_username:$nfx_password\@sip.nfx.czf/cgi-bin/admin/lbilling/soap.pl";
$lbilling = lBilling->new();
unless ( $lbilling ) {
print "Could not create SOAP instance\n";
if ( $email_alert == 1 ){
&send_email("Could not create SOAP instance");
}
exit 1;
}
if ( exists( $opts{"s"} ) and $opts{"s"} ) {
&synchronize_all();
}
if ( exists( $opts{"p"} ) and $opts{"p"} ) {
&update_payments();
}
# -------------------------------------------------------------------------
#synchronize account and subscribers
sub synchronize_all {
my ( $sth1, $sth2, $user, $link, $result, $all, $descr );
$all = [];
unless( $sth1 = $dbh->prepare("SELECT * FROM voip_lbilling_users") ) {
&logging("Can't prepare statement SELECT * FROM voip_lbilling_users " . $dbh->errstr);
if ( $email_alert == 1 ){
&send_email("Can't prepare statement SELECT * FROM voip_lbilling_users " . $dbh->errstr);
}
die;
}
unless ( $sth1->execute ) {
&logging("Can't execute statement SELECT * FROM voip_lbilling_users" . $dbh->errstr);
if ( $email_alert == 1 ){
&send_email("Can't execute statement SELECT * FROM voip_lbilling_users" . $dbh->errstr);
}
die;
}
while ( $user = $sth1->fetchrow_hashref ) {
$descr = &$czech_to_ascii($user->{"descr"});
$descr =~ s/[^\-\.\_\w ]/ /isg;
$descr = substr($descr,0,255);
my $member = { "billingid" => $user->{"id"},
"currency" => $user->{"currency"},
"type" => $user->{"type"},
"state" => $user->{"state"},
"limit" => $user->{"limit"},
"descr" => $descr,
"subscribers" => []
};
unless( $sth2 = $dbh->prepare("SELECT * FROM voip_lbilling_accounts WHERE userid = ".$user->{"id"}." ORDER BY id" ) ) {
&logging("Can't prepare statement SELECT * FROM voip_lbilling_accounts" . $dbh->errstr);
if ( $email_alert == 1 ){
&send_email("Can't prepare statement SELECT * FROM voip_lbilling_accounts" . $dbh->errstr);
}
die;
}
unless ( $sth2->execute ) {
&logging("Can't execute statement SELECT * FROM voip_lbilling_accounts" . $dbh->errstr);
if ( $email_alert == 1 ){
&send_email("Can't execute statement SELECT * FROM voip_lbilling_accounts" . $dbh->errstr);
}
die;
}
while ( $link = $sth2->fetchrow_hashref ) {
# unless ( $link->{"id"} =~ /^$link->{"userid"}\d{2}$/is ) {
# &logging("Userid not match Link id!");
# next;
# }
$descr = &$czech_to_ascii($link->{"descr"});
$descr =~ s/[^\-\.\_\w ]/ /isg;
$descr = substr($descr,0,255);
push( @{$member->{"subscribers"}},
{ "billingid" => $link->{"id"},
"cid" => "420" . $link->{"cid"},
"state" => $link->{"state"},
"limit" => $link->{"limit"},
"tarif" => $link->{"tarif"},
"descr" => $descr
} );
}
$sth2->finish();
push( @{$all}, $member );
}
$sth1->finish();
# use Data::Dumper;
# print Dumper( $lbilling );
if ( $result = $lbilling->synchronize_all($all) ) {
&logging("synchronize all OK");
&logging(Dumper($result));
#&logging(Dumper($all));
return 0;
} else {
&logging("Synchronize all FAILED!");
&logging(Dumper($all));
&logging(Dumper($lbilling->get_error()));
if ( $email_alert == 1 ){
&send_email("Synchronize all FAILED!
".Dumper($lbilling->get_error()));
}
return 1;
}
}
# -------------------------------------------------------------------------
#synchroniza payments
sub update_payments {
my ( $sth, $payment, $descr );
unless( $sth = $dbh->prepare("SELECT * FROM voip_lbilling_payments") ) {
&logging("Can't prepare statement SELECT * FROM voip_lbilling_payments " . $dbh->errstr);
if ( $email_alert == 1 ){
&send_email("Can't prepare statement SELECT * FROM voip_lbilling_payments " . $dbh->errstr);
}
die;
}
unless ( $sth->execute ) {
&logging("Can't execute statement SELECT * FROM voip_lbilling_payments " . $dbh->errstr);
if ( $email_alert == 1 ){
&send_email("Can't execute statement SELECT * FROM voip_lbilling_payments " . $dbh->errstr);
}
die;
}
while ( $payment = $sth->fetchrow_hashref() ) {
unless ( $lbilling->get_account( {"billingid" => $payment->{"userid"} } ) ) {
&logging("ballance added FAILED! - account " . $payment->{"userid"} . " is not in billing");
if ( $email_alert == 1 ){
&send_email("Ballance added FAILED! - account " . $payment->{"userid"} . " is not in billing");
}
next;
}
$descr = &$czech_to_ascii($payment->{"description"});
$descr =~ s/[^\-\.\_\w ]/ /isg;
$descr = substr($descr,0,255);
my $ballance = { "account" => $payment->{"userid"},
"billingid" => $payment->{"id"},
"value" => int($payment->{"value"}),
"descr" => $descr
};
# use Data::Dumper;
# print Dumper( $lbilling );
if ( $lbilling->add_ballance($ballance) ) {
&logging("ballance added OK");
unless ( $dbh->do("UPDATE transfers SET type = 4 WHERE id = ".$payment->{"id"}) ) {
&logging();
}
} else {
&logging("Ballance added FAILED!");
&logging(Dumper($ballance));
&logging(Dumper($lbilling->get_error()));
if ( $email_alert == 1 ){
&send_email("Ballance added FAILED!
".Dumper($lbilling->get_error()));
}
}
}
$sth->finish();
}
# -------------------------------------------------------------------------
# logging
sub logging {
my ( $message ) = @_;
my ( $time, $i );
if ( not open(LOG, $log_file) ) {
die;
}
$time = localtime(time);
if ( $file_lock ) { flock(LOG, 2); }
print LOG "$time \t $message \r\n";
close(LOG);
}
# -------------------------------------------------------------------------
#send e-mail
sub send_email {
my ( $messages ) = @_;
# perl -MCPAN -e shell
# install MIME::Lite::TT::HTML
my %params;
$params{first_name} = 'FreenetIS billing';
$params{message} = $messages;
my %options;
$options{INCLUDE_PATH} = '/usr/local/bin/lbilling';
my $msg = MIME::Lite::TT::HTML->new(
From => $email_address_from,
To => $email_address_to,
Subject => 'FreenetIS - Problem with billing synchronization',
Template => {
html => 'html.tt',
},
TmplOptions => \%options,
TmplParams => \%params,
);
$msg->send('smtp', $email_smtp, Timeout => 5 );
}
