[root@home bin]# cat importcdrs.pl 
#!/usr/bin/perl
#########################################
# FILE: inportcdrs.pl
# ARG0: type
# ARG1: file
# By: Gregory D. Burns
#########################################
#

### Load Drivers ###
use Switch;     # For the Switch function
use POSIX;      # For rounding
use DBI();      # For connecting to the Database
use File::Tail; # For tailing the file

### Set Var ###
my $type     = $ARGV[0];
my $filename = $ARGV[1];
our $loggerfile  = "/root/logger/importcdrs.txt";
my $dbname   = "Tigris";
my $host     = "localhost";
my $dbuser   = "TGcdrs";
my $dbpass   = "cdrs\@TG4";
our $max_len = 0;

### Connect to DB ###
my $dbh = DBI->connect("DBI:mysql:$dbname:$host", "$dbuser", "$dbpass") || die "ERROR Connecting to DB: $DBI::errstr\n";

### Connect to File ###
if ($type eq "pull")
{
  my $ref=tie *FH,"File::Tail",(name=>$filename,debug=>0,interval=>1,maxinterval=>5,adjustafter=>10,errmode=>"warn");
} else {
  open(FH,"<$filename") || die "Could not open $filemane\n";
}

### Load Customers RateTable into memory ###
$qid = $dbh->prepare("SELECT id,ratetable FROM customers");
$qid->execute;
while ( my($id,$custrt) = $qid->fetchrow_array )
{
   $custrt_h{$id} = "$custrt";
}

### Load Rate table into memory ###
$qid = $dbh->prepare("SELECT name,code,codename,start_ts,stop_ts,rate,inc1,inc2 FROM rates");
$qid->execute;
while ( my($rt,$code,$codename,$start_ts,$stop_ts,$rate,$inc1,$inc2) = $qid->fetchrow_array )
{
   if (substr($code,0,1) ne "1") { $code = "011" . $code; }
   if (length($code) > $max_len) { $max_len = length($code);}
     push @{$rate_h{$code}}, "$rt:$codename:$start_ts:$stop_ts:$rate:$inc1:$inc2";
}

logger("*** Program Started ***");

### Start Looping through file ###
while (<FH>)
{
  chop();                     # Chop off end line return
  if($_ eq "EOF") { exit;}    # If EOF, quit
  if($_ eq "") {next;}        # If blank line, skip.
  if($_ eq "BOF") {next;}     # If BOF line, skip.

  ### Split out var from line ###
  my($timestamp,$sip_hf_cseq,$sip_hf_callid,$uid,$sip_srcip,$sip_user,$sip_hf_route) = split(",", $_);

  switch ($sip_hf_cseq) {
    case /INVITE/ {
      $rate_grp = get_rate($uid,$sip_user,$timestamp);
      my($codename,$rate) = split(/:/,$rate_grp);
      $sql = "UPDATE cdrs set active='N' WHERE uid='$uid' and active='Y'";
      $dbh->do($sql);
      $sql = "REPLACE INTO cdrs (id,active,uid,fromip,number,start,codename,rate) VALUES ('$sip_hf_callid','Y','$uid','$sip_srcip','
$sip_user','$timestamp','$codename','$rate')";
      $dbh->do($sql);
      logger($sql);
    }
    case /REFER/  {
      $rate_grp = get_rate($uid,$sip_user,$timestamp);
      my($codename,$rate) = split(/:/,$rate_grp);
      $sql = "REPLACE INTO cdrs (id,active,uid,fromip,number,start,codename,rate) VALUES ('$sip_hf_callid','Y','$uid','$sip_srcip','
$sip_user','$timestamp','$codename','$rate')";
      $dbh->do($sql);
      logger($sql);
    }
    case /ACK/    {
      if ($sip_hf_route eq "")
      {
        $sql="UPDATE cdrs SET end='$timestamp',active='N' where id='$sip_hf_callid'";
        $dbh->do($sql);
        logger($sql);
      }else{
        my($trash,$trash,$route)=split(/([@>])/,$sip_hf_route);
        $sql="UPDATE cdrs SET bill='$timestamp',toip='$route' where id='$sip_hf_callid'";
        $dbh->do($sql);
        logger($sql);
      }
    }
    case /BYE/    {

      my $qid = $dbh->prepare("SELECT dur FROM cdrs WHERE id='$sip_hf_callid'");
      $qid->execute;
      my($dur) = $qid->fetchrow_array;

      if ($dur == '0') {
        $rate_grp = get_rate($uid,$sip_user,$timestamp);
        my($codename,$rate) = split(/:/,$rate_grp);
        my $qid = $dbh->prepare("SELECT bill,start FROM cdrs WHERE id='$sip_hf_callid'");
        $qid->execute;
        my($bill,$start) = $qid->fetchrow_array;

        if ( $bill == '0') { $bill = $start;}

        $dur = ($timestamp - $bill);
        if ($dur < 30) {
          $dur=$inc1;
        }else{
          $inc2m = ceil($dur / 6);
          $dur = (6 * $inc2m);
        }
       
          $cost = ($rate * ($dur/60));
          $sql="UPDATE cdrs SET end='$timestamp',active='N',dur='$dur',cost='$cost' where id='$sip_hf_callid'";
          $dbh->do($sql);
          logger($sql);

          # Update Customers amount #
          $sql="SELECT custid where uid='$uid'";
          my $qid = $dbh->prepare("SELECT custid from devices where uid='$uid'");
          $qid->execute;
          my($custid) = $qid->fetchrow_array;

          $sql="UPDATE customers set balance=(balance-'$cost') where id='$custid'";
          $dbh->do($sql);
          logger($sql);

          my $qid = $dbh->prepare("SELECT balance from customers where id='$custid'");
          $qid->execute;
          my($balance) = $qid->fetchrow_array;

          if($balance < 0) { $sql = "UPDATE customers set status='Low Funds' where id='$custid'"; $dbh->do($sql); logger($sql);}
      }else{
        $sql="UPDATE cdrs SET end='$timestamp',active='N',dur='0',cost='0' where id='$sip_hf_callid'";
        $dbh->do($sql);
      }#END->if ($dur > 0) {
    }
    case /CANCEL/ {
      $sql="UPDATE cdrs SET end='$timestamp',active='N' where id='$sip_hf_callid'";
      $dbh->do($sql);
      logger($sql);
    }
    else          { print "ERROR UNDEF CALL REC $sip_hf_cseq\n";}
  }#END->switch ()
}#END->while()

#######################################################################################################
# SUB: get_rate
#######################################################################################################
#
sub get_rate {
 
  my ($uid,$number,$timestamp) = @_;
  $custid = substr($uid,0,3);
  $cust_rt = $custrt_h{$custid};
  if (length($number) > 6) {
    $x = $max_len;
    $rate_found = 0;
    our $prefix = substr($number,0,$x);

    while (!($rate_h{$prefix}) && ($x > 0)) { $prefix = substr($prefix,0,--$x);}

    while ((! $rate_found) && ($x > 0)) 
    {
      for $i (0 .. $#{ $rate_h{$prefix} }) 
      {
        $frate = $rate_h{$prefix}[$i];
        our($rt,$codename,$start_ts,$stop_ts,$rate,$inc1,$inc2) = split(/:/,$frate);
        if (($start_ts < $timestamp) && (($stop_ts == 0) || ($stop_ts > $timestamp))) {
          if ($cust_rt eq $rt)
          {
            $rate_found = 1;
            $rate_grp= "$codename : $rate";
            $break;
          }#END->if ($cust_rt eq $rt)
        }#END->if (($start_ts < $timestamp) && (($stop_ts == 0) || ($stop_ts > $timestamp))) 
      }#END->for $i (0 .. $#{ $rate_h{$prefix} })
      if (!$rate_found) 
      {
        $prefix = substr($prefix,0,--$x);
        while (!($rate_h{$prefix}) && ($x > 0)) { $prefix = substr($prefix,0,--$x);}
      }#END-> if (!$rate_found)
    }
  } else {
    # ON-NET CALL
    $rate_grp = "On-Net : 0.0";
    $rate_found=1;
  }#END->if (length($number) > 9)

  if (! $rate_found) { $rate_grp = "Unknow : 0.0"; }
  logger($rate_grp);
  return ($rate_grp);

}

#######################################################################################################
# SUB: logger
#######################################################################################################
#
sub logger  {
  my($desc) = @_;

  $now_string = localtime;
  open(LOG,">> $loggerfile") || die "Could not open $loggerfile\n";
    print LOG "$now_string: $desc\n";
  close(LOG);
}
