# !/usr/local/bin/perl -w
use strict;
use warnings;
use DBI;
use Data :: Dumper;
use Net :: FTP;
my $filepath = " ./lbsdata " ;
print " unload begins. " ;
my $title = " #PROVINCE CITY SITE_ID MICRO? LAC CELL_ID BSIC BCCH X_deg X_min X_sec Y_deg Y_min Y_sec BORE HEIGHT(m) EIRP BA_IND NUM_BA BA_1 BA_2 BA_3 BA_4 BA_5 BA_6 BA_7 BA_8 BA_9 BA_10 BA_11 BA_12 BA_13 BA_14 BA_15 BA_16 BA_17 BA_18 BA_19 BA_20 BA_21 BA_22 BA_23 BA_24 BA_25 BA_26 BA_27 BA_28 BA_29 BA_30 BA_31 BA_32 BA_IND NUM_BA BA_1 BA_2 BA_3 BA_4 BA_5 BA_6 BA_7 BA_8 BA_9 BA_10 BA_11 BA_12 BA_13 BA_14 BA_15 BA_16 BA_17 BA_18 BA_19 BA_20 BA_21 BA_22 BA_23 BA_24 BA_25 BA_26 BA_27 BA_28 BA_29 BA_30 BA_31 BA_32 " ;
$title =~ s / s +/ t / g;
my $currentTime = & getTimestamp( 1 );
$currentTime = substr $currentTime , 0 , 8 ;
print " date: $currentTime " ;
my $filePath = ' /usr/local/apache2/htdocs/irms/BOCO.IRMS/local/bin/lbsdata ' ;
my $ftpfilePath = ' /data/bsdata ' ;
my $ftpfilePath2 = ' /opt/edis/lbs ' ;
print " file name: $filePath/sc$currentTime.txt " ;
open FILE , " >$filePath/sc$currentTime.txt " or die " open file failed: $! " ;
print FILE $title , " " ;
print " connect to database: " ;
my $dbh = & dbconnect();
my $sql = q / select ' sichuan '
-- b . toll_code[ 2 , 5 ] , 区号没有找到对应
-- CITY 838
, s . AREA_CODE
, b . BTS_ID
, decode(c . CELL_TYPE , 1 , ' Y ' , 2 , ' N ' , ' Y ' )
, c . LAC AS LAC
, c . CI AS CELL_ID
, NVL(c . NC_CODE || c . CC_CODE , 50 ) AS BSIC
, c . BCCH AS BCCH
, substring(c . LONGITUDE from 1 for 8 ) AS X_deg
, ' 0 ' as X_min
, ' 0 ' as X_sec
, substring(c . LATITUDE from 1 for 8 ) as Y_deg
, ' 0 ' as Y_min
, ' 0 ' as Y_sec
, c . ANT_AZIMUTH as BORE
, trunc(c . ANT_HEIGHT) AS HEIGHT
, c . TRX_POWER AS EIRP
--, c . BA_IND_IDEL AS BA_IND
, 1 as BA_IND
, c . BA_NUM_IDEL AS NUM_BA
, c . BA_IDLE1 AS BA1
, c . BA_IDLE2 AS BA2
, c . BA_IDLE3 AS BA3
, c . BA_IDLE4 AS BA4
, c . BA_IDLE5 AS BA5
, c . BA_IDLE6 AS BA6
, c . BA_IDLE7 AS BA7
, c . BA_IDLE8 AS BA8
, c . BA_IDLE9 AS BA9
, c . BA_IDLE10 AS BA10
, c . BA_IDLE11 AS BA11
, c . BA_IDLE12 AS BA12
, c . BA_IDLE13 AS BA13
, c . BA_IDLE14 AS BA14
, c . BA_IDLE15 AS BA15
, c . BA_IDLE16 AS BA16
, c . BA_IDLE17 AS BA17
, c . BA_IDLE18 AS BA18
, c . BA_IDLE19 AS BA19
, c . BA_IDLE20 AS BA20
, c . BA_IDLE21 AS BA21
, c . BA_IDLE22 AS BA22
, c . BA_IDLE23 AS BA23
, c . BA_IDLE24 AS BA24
, c . BA_IDLE25 AS BA25
, c . BA_IDLE26 AS BA26
, c . BA_IDLE27 AS BA27
, c . BA_IDLE28 AS BA28
, c . BA_IDLE29 AS BA29
, c . BA_IDLE30 AS BA30
, c . BA_IDLE31 AS BA31
, c . BA_IDLE32 AS BA32
--, c . BA_IND_BUSY AS BA_IND
, 0 as BA_IND
, c . BA_NUM_IDEL AS NUM_BA
, c . BA_IDLE1 AS BA1
, c . BA_IDLE2 AS BA2
, c . BA_IDLE3 AS BA3
, c . BA_IDLE4 AS BA4
, c . BA_IDLE5 AS BA5
, c . BA_IDLE6 AS BA6
, c . BA_IDLE7 AS BA7
, c . BA_IDLE8 AS BA8
, c . BA_IDLE9 AS BA9
, c . BA_IDLE10 AS BA10
, c . BA_IDLE11 AS BA11
, c . BA_IDLE12 AS BA12
, c . BA_IDLE13 AS BA13
, c . BA_IDLE14 AS BA14
, c . BA_IDLE15 AS BA15
, c . BA_IDLE16 AS BA16
, c . BA_IDLE17 AS BA17
, c . BA_IDLE18 AS BA18
, c . BA_IDLE19 AS BA19
, c . BA_IDLE20 AS BA20
, c . BA_IDLE21 AS BA21
, c . BA_IDLE22 AS BA22
, c . BA_IDLE23 AS BA23
, c . BA_IDLE24 AS BA24
, c . BA_IDLE25 AS BA25
, c . BA_IDLE26 AS BA26
, c . BA_IDLE27 AS BA27
, c . BA_IDLE28 AS BA28
, c . BA_IDLE29 AS BA29
, c . BA_IDLE30 AS BA30
, c . BA_IDLE31 AS BA31
, c . BA_IDLE32 AS BA32
from RADIO_CELL_G c , OUTER RADIO_BTS_G b , OUTER SWITCH_MSC s
where c . BTS_ID = b . cuid
and c . LONGITUDE is not null
and c . LATITUDE is not null
-- and c . confirmed = 1
and c . BTS_ID is not null
and c . BTS_ID <> ''
and c . MSC_ID = s . CUID / ;
print " sql: $sql " ;
# is_micro_cell
my $ADJ_CELL_POSITION = 19 ; # position of Ba_idle1
my $ANT_DIRCT_ANGLE_POSITION = 14 ;
my $IS_MICRO_POSITION = 3 ;
# #############################################
##BSIC_POSITION modify 2007-12-18
##############################################
my $BSIC_POSITION = 6 ;
# default 50
#my @DEFAUT_50_POSITION = ( 6, 7, 14, 15, 16 );
# longitude, latitude
my $LONGITUDE_POSITION = 8 ;
my $LATITUDE_POSITION = 11 ;
my $sth = $dbh -> prepare( $sql )
or die " prepare failed: $sql " ;
$sth -> execute();
my @data ;
my $line = 0 ;
while ( @data = $sth -> fetchrow_array() ) {
$line ++ ;
# print "$line unload. ";
foreach ( @data ) { # remove tail white spaces
if ( defined $_ ) {
s / s + $ // ;
}
}
@data = & convertData( @data );
print FILE join ( " " , @data ) , " " ;
# if( $line == 7214 ) {
# print Dumper @data;
# last;
# }
# exit;
}
print " total $line unloaded. " ;
print " unload finish. " ;
close FILE;
$sth -> finish();
$dbh -> disconnect();
print " transfer data via FTP... " ;
& ftpPut();
& ftpPut2();
exit ;
sub dbconnect {
my $dbh ;
my $dsn = ' dbi:Informix:tnms_sc@edisdb1 ' ;
my $username = ' informix ' ;
my $password = ' boco#informix ' ;
print " dsn: $dsn " ;
eval { $dbh = DBI -> connect ( $dsn , $username , $password , { AutoCommit => 1 ,
RaiseError => 1 ,
odbc_cursortype => 2 } );
};
if ($@) {
print " can't connect to DSN: $dsn. " . " $DBI::errstr, $@ " ;
die " can't connect to DSN: $dsn. " . " $DBI::errstr " ;
}
return $dbh ;
}
sub isNull {
if ( defined $_ [ 0 ] and $_ [ 0 ] !~ /^ s * $ / ) {
return 0 ;
} else {
return 1 ;
}
}
sub convertData {
my @data = @_ ;
if ( not @data ) {
print " data(line) is null " ;
exit ;
}
# print Dumper @data;
# $data[0] = 'sichuan';
# is_micro_cell
if ( & isNull( $data [ $ADJ_CELL_POSITION ]) or & isNull( $data [ $ANT_DIRCT_ANGLE_POSITION ]) ) {
# print "$data[$ADJ_CELL_POSITION], $data[$ANT_DIRCT_ANGLE_POSITION] ";
# print "Y ";
$data [ $IS_MICRO_POSITION ] = ' Y ' ;
} else {
# print "$data[$ADJ_CELL_POSITION], $data[$ANT_DIRCT_ANGLE_POSITION] ";
# print "N ";
$data [ $IS_MICRO_POSITION ] = ' N ' ;
}
# #################################
####ant_dirct_angle modify###
#my $ant_dirct_angle = $data[14] + 0;
#if ($ant_dirct_angle < 0) {
# my $new_antdirctangle = 360 - abs($ant_dirct_angle);
# $data[14] = $new_antdirctangle;
#}
##########
#判断 0 < BSIC < 63 modify 2007-12-18
my $vlaue = $data [ 6 ] + 0 ; # string to int
if ( $vlaue < 0 or $vlaue > 63 ) {
$data [ 6 ] = " 50 " ;
}
# 判断BSIC为空
if ( & isNull( $data [ 6 ]) ) {
$data [ 6 ] = 50 ;
}
# 判断BCCH为空
if ( & isNull( $data [ 7 ]) ) {
$data [ 7 ] = 50 ;
}
# 判断x_min
if ( & isNull( $data [ 9 ])) {
$data [ 9 ] = 0 ;
}
# 判断x_sec
if ( & isNull( $data [ 10 ])) {
$data [ 10 ] = 0 ;
}
# set y_min = 0
if ( & isNull( $data [ 12 ])) {
$data [ 12 ] = 0 ;
}
# set y_sec = 0
if ( & isNull( $data [ 13 ])) {
$data [ 13 ] = 0 ;
}
# 判断BORE范围0-----360
if ( $data [ 14 ] lt 0 or $data [ 14 ] gt 360 or & isNull( $data [ 14 ])) {
$data [ 14 ] = 0 ;
}
# 判断HEIGHT范围>0
my $value15 = $data [ 15 ] + 0 ;
if ( $value15 < 0 or $value15 = 0 or & isNull( $data [ 15 ]) ) {
$data [ 15 ] = 50 ;
}
$data [ 15 ] = int ( $value15 ) + 1 ;
# 判断EIRP范围>0
my $value16 = $data [ 16 ] + 0 ;
$data [ 16 ] = int ( $value16 ) + 1 ;
if ( $value16 <= 0 ) {
$data [ 16 ] = 50 ;
}
if ( & isNull( $data [ 16 ])) {
$data [ 16 ] = 50 ;
}
if ( $value16 > 0 ) {
$data [ 16 ] = int ( $value16 ) + 1 ;
}
# 判断NUM_BA范围0--32
my $value18 = $data [ 18 ] + 0 ;
if ( $value18 < 0 or $value18 > 32 or & isNull( $data [ 18 ]) ) {
$data [ 18 ] = 0 ;
}
# 判断NUM_BA范围0--32
my $value52 = $data [ 52 ] + 0 ;
if ( $value52 < 0 or $value52 > 32 ) {
$data [ 52 ] = 0 ;
}
if ( & isNull( $data [ 52 ]) ) {
$data [ 52 ] = 0 ;
}
# ##################################
# exit;
#$data[14] =~ s/_$//;
# if( $data[14] =~ /^-/ ) {
# $data[14] = 0;
# }
# default 50
# foreach my $position ( @DEFAUT_50_POSITION ) {
# if( &isNull($data[$position]) ) {
# $data[$position] = 50;
# }
# }
# latitude, longitude
# foreach( ($LONGITUDE_POSITION, $LATITUDE_POSITION) ) {
# if( $data[$_] =~ /^d+.d{6}/ ) {
# $data[$_] = $&;
# }
# }
# 0.00000E+00
# for( my $i = 0; $i < scalar @data; $i++ ) {
# if( $i > 17 and &isNull($data[$i]) ) {
# #$data[$i] = '33237_10242';
# $data[$i] = ' ';#modify 2007-12-18
# } elsif( not &isNull($data[$i]) and $data[$i] =~ /^0.00000E+00$/ ) {
# $data[$i] = 0;
# }
# }
# print Dumper @data;
return @data ;
}
sub getTimestamp () {
my $format = shift ;
my ( $sec , $min , $hour , $day , $mon , $year ) = localtime ( time );
$mon += 1 ;
$year += 1900 ;
my $time ;
if ( defined $format ) {
if ( $format == 1 ) {
# if ( defined $format ) {
$time = sprintf " %04d%02d%02d%02d%02d%02d " , $year , $mon , $day , $hour , $min , $sec ;
} else {
$time = sprintf " %04d-%02d-%02d %02d:%02d:%02d " , $year , $mon , $day , $hour , $min , $sec ;
}
} else {
$time = sprintf " %04d-%02d-%02d %02d:%02d:%02d " , $year , $mon , $day , $hour , $min , $sec ;
}
$time ;
}
# #################################################################
# put filesvia FTP
# Interface: ftpPut();
# Return: None
###################################################################
sub ftpPut() {
my $count = 0 ; # 用来记录失败的次数
my $line ; # 用来按照行读取日志文件
my $host = ' 10.105.228.91 ' ;
my $usr = ' bsdata ' ;
my $passwd = ' bsdata!23 ' ;
print " ftp 10.105.228.91 begins; " ;
my $ftp = Net :: FTP -> new( $host , Debug => 0 );
$ftp -> login( $usr , $passwd ) or die " connect failed: $! " ;
# $filePath/sc$currentTime.txt;
$ftp -> put( " $filePath/sc$currentTime.txt " , " $ftpfilePath/sc$currentTime.txt " ) # save to task data directory
or die " cannot put the file sc$currentTime.txt " ;
# ###################################################################
#try FTP again
print " now read log by line... " ;
my $logfile = " putlbs.log " ;
open MYFILE , $logfile || die " cant open $logfile " ;
while ( $line = < MYFILE > ) {
if ( $line =~ / cannot / ) {
# print "$line ";
$count ++ ;
}
}
close MYFILE;
print " read finish... " ;
print " 失败的次数$count " ;
if ( $count > 0 && $count < 4 ) {
print " try ftp again... " ;
& ftpPut();
& ftpPut2();
} else {
print " ftp 10.105.228.91 finish " ;
}
# ###################################################################
print " put sc$currentTime.txt successfully. " ;
return ;
}
sub ftpPut2() {
my $host = ' 10.101.9.48 ' ;
my $usr = ' edis ' ;
my $passwd = ' boco#edis ' ;
print " ftp to 10.101.9.48 begins; " ;
my $ftp = Net :: FTP -> new( $host , Debug => 0 );
$ftp -> login( $usr , $passwd ) or die " connect failed: $! " ;
# $filePath/sc$currentTime.txt;
$ftp -> put( " $filePath/sc$currentTime.txt " , " $ftpfilePath2/sc$currentTime.txt " ) # save to task data directory
or die " cannot put the file sc$currentTime.txt " ;
print " put to 10.101.9.48 sc$currentTime.txt successfully. " ;
return ;
}