#!/usr/bin/perl

#use blib;
use Net::FTP;
use Getopt::Long;
use Net::MySQL;
use Data::Dumper;

# use strict;

use File::Data;
use Date::Manip;

my $DB_server= "localhost";
my $DB_database="sonysms";
my $DB_user="sony";
my $DB_pass="thienvinh";
my $DB_port=3306;

$filename = "";
$filename_short="";

$opt_debug = undef;
$opt_firewall = undef;
#$host = "10.253.0.6";
#$user = "tin";
#$password = "thienvinh";

$host = "192.168.31.10";
$user = "sony";
$password = "sonyvn";
$passive = 1;

GetOptions(qw(debug firewall=s));

@firewall = defined $opt_firewall ? (Firewall => $opt_firewall) : ();

     buildmobilecdr();
     if ( $filename ne "" )
     {
          $ftp = Net::FTP->new($host, @firewall, Debug => $opt_debug ? 1 : 0, Passive => $passive ? 1 : 0)
                      or die "Cannot connect to some.host.name: $@";
          $ftp->login($user,$password)
                        or die "Cannot login ", $ftp->message;
#print $ftp->pwd,"\n";
#    $ftp->cwd("/pub")
#      or die "Cannot change working directory ", $ftp->message;
          $ftp->binary();
#           my $dir = "/var/sms";
#           system("cd /var/sms/temp");
           print($filename);
           print("\n");
           $ftp->put ("cdr/".$filename_short);
#           system("cd %dir");
           $ftp->quit;
     }
#     print ("\ndelete file (sent): $filename\n");
#     system("rm -f $filename");

sub buildmobilecdr()
{
        ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
        $year = ($year + 1900);
# $year_2 = ($year - 2000);
#if ($year_2 < 10) {$year_2 = "0$year_2";}
       $mon++;
       if ($mon < 10) {$mon = "0$mon";}
       if ($mday < 10) {$mday = "0$mday";}
       if ($hour < 10) {$hour = "0$hour";}
       if ($min < 10) {$min = "0$min";}
       if ($sec < 10) {$sec = "0$sec";}
       $now_date_epoch = time();
       $date_where = "$year-$mon-$mday $hour:$min:$sec";
       $now_date = "$year-$mon-$mday $hour:$min:$sec";
#       $now_date_replay = "$mday/$mon/$year_2 $hour:$min";
       $now_date_file = "$year$mon$mday$hour$min$sec";
       $filename_short = "561561_$now_date_file.txt";
       $filename = "/var/sms/smog/cdr/$filename_short";
        system("echo > $filename");

        my $rec_count;
        my $lastdate;
        my $uniqueid;
        my $date_;
        my $date1_;
        my $temp;
        my @date_day;
        my @date_time;
        my $fone;
        my $content;
        my $strsql;
        my $dataline;

        $lastdate = "";

        my $mysql = Net::MySQL->new(hostname=>"$DB_server", database=>"$DB_database", user=>"$DB_user", password=>"$DB_pass", port => "$DB_port") or         die "Couldn't connect to database: $DB_server - $DB_database\n";
        $strsql = "select date_,amount,description from cdrtracking order by date_ desc limit 1";
        print "\n$strsql\n";
        $mysql->query($strsql);
        $rec_count = 0;
        if ($mysql->has_selected_record)
        {
                my $iter=$mysql->create_record_iterator;
                while ( $record = $iter->each)
                 {
                        $rec_count++;
                        $lastdate = "$record->[0]";
                        print "\nrecord: $rec_count, last ftp: $lastdate\n";
                }#while ( $record = $iter->each)
         }#end have record

         if ( $lastdate eq "" )
         {
              $lastdate = "1970-01-01 00:00:00";
              $strsql ="select uniqueid,date_,fone,content,sent from smslog where sent = 0 and (fone like '8491%' or fone like '91%')";
         }
         else
         {
             $strsql ="select uniqueid,date_,fone,content,sent from smslog where sent = 0 and date_ > '$lastdate' and (fone like '8491%' or fone like '91%')";
         }
        print "\n$strsql\n";
        $mysql->query($strsql);
        $rec_count = 0;

        my $o_dat = File::Data->new($filename);

        if ($mysql->has_selected_record)
        {
                my $iter=$mysql->create_record_iterator;
                while ( $record = $iter->each)
                 {
                        $rec_count++;
                        $uniqueid = "$record->[0]";
                        $date_ = "$record->[1]";
                        $lastdate = $date_;
                        $fone = "$record->[2]";
                        $content = "$record->[3]";

                       print "\nrecord: $rec_count, date: $date_ \n";
                        @date1_ = split " ",$date_;
#                        print ("\n$date1_[0],$date1_[1]\n");
                        $temp = $date1_[0];
                        @date_day = split "-",$temp;
                        $temp = $date1_[1];
                        @date_time = split ":",$temp;
                        $date_ = join("",@date_day);
                        $date1_ = join("",@date_time);
#                        print ("\n$date_,$date1_\n");
                        $temp = $date_;
                        $date_ = $temp.$date1_;
#                        print ("\n$date_\n");
                        $dataline = "M\t841900561561\t$fone\t$date_\tD\t$date_\t0\tMAPMO.1:1\t0\tMAP.1:1\t1/160\t6";
#                        print("\n$dataline\n");
                        $o_dat->append("$dataline\n");
                        #### write to data file
                }#while ( $record = $iter->each)
         }#end have record

         if ( $rec_count eq 0 )
         {
             system("rm -f $filename");
             print ("\ndelete file (0): $filename\n");
             $filename = "";
         }
         else
         {
                  #update to cdrtracking
                 $strsql = "insert into cdrtracking(date_,amount,description) values ('$lastdate',$rec_count,'ftp to mobile for charge');";
                 print "\n$strsql\n";
                 $mysql->query($strsql);
         }

         $mysql->close();

}

sub createfile()
{

#$string = "0913654857";
#if ( ($string =~ /^8491/i) or ($string =~ /^091/i) )
#{
#      print ("\n$string\n");
#}

        my $filename = "/var/sms/smog/abc.txt";

#        system("cat > $filename");
        system("echo > $filename");
        my $o_dat = File::Data->new($filename);
#        my $o_dat = File::Data->new('/var/sms/smog/abc.txt','rw');

        $o_dat->write("complete file contents\n");
        $o_dat->prepend("first line\n"); # line 0
        $o_dat->append("original second (last) line\n");
        $o_dat->insert(2, "new second line\n"); # inc. zero!
        $o_dat->append("end1\n");
        $o_dat->append("end2\n");
        $o_dat->append("$filename\n");
#        my $i_closed = $o_dat->close;
#        $o_dat->replace('line', 'LINE');
#        print $o_dat->READ;
}