Zobrazení zdroje stránky 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!<br />".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!<br />".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 );
 }