Lbilling-synchronization.pl

#!/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 );
}