[zz] perl 操作数据库

#!/usr/local/bin/perl

#==================================================================
#  @(#) Perl Module:
#
#  Author(s):czw1413_cn

#
#  Creation Date:2007-07-21
#
#  Description:This program do with the data
#
#==================================================================

use Date::Format;
use Time::ParseDate;
use Date::Manip;
use DBI;
use strict;

#========Get insert ARGV
my $var=$ARGV[0];
$var =~ m/(\d*)\-(\d*)\-(\d*)/g;
my $day=$2."/".$3."/".$1;

#========Connect Informix server
my $dbh = DBI->connect('DBI:Informix:npmdb@npmserver','informix','informix') || die "Database connection not made: $DBI::errstr";

#=======Get fact busy hour
my $sql_max =  qq{ select max(a_interface_traf) from tpa_unic_msc_sum where compress_date=\'$day\' and ne_type=10000 and sum_level=0 and sv_id=-1 };
my $sth = $dbh->prepare($sql_max);
$sth->execute();
my $max_traf;
$sth->bind_columns(undef,\$max_traf);
$sth->fetch();
$sth->finish();

my $sql_fhour = qq{ select max(first_result) from tpa_unic_msc_sum where compress_date=\'$day\' and ne_type=10000 and sum_level=0 and sv_id=-1 and a_interface_traf=$max_traf };
my $sth = $dbh->prepare($sql_fhour);
$sth->execute();
my $f_hour;
$sth->bind_columns(undef,\$f_hour);
$sth->fetch();
print "The ".$var." busy hour is ".$f_hour."\n";
$sth->finish();

#=======Get busy hour's bsc and Do with its
my $sql_bscid = qq{ select ne_id from tpa_unic_bts_sum where ne_type=200 and first_result='$f_hour' and sum_level=0 and sv_id=-1 };
my $sth = $dbh->prepare($sql_bscid);
$sth->execute();
my $bsc_id;
$sth->bind_columns(undef,\$bsc_id);
print "Now these BSC Updated\n";
my $count=0;
while ( $sth->fetch() )
        {
        my $sql_g47 = qq{ select SFB_DIVFLOAT_1(tch_seize_nho,tch_call_req_nho,0,0)*100,tch_seize_nho,tch_call_req_nho from tpa_unic_bts_sum where first_result='$f_hour' and ne_id=$bsc_id and ne_type=200 and sv_id=-1 and sum_level=0 };
        my $sth =  $dbh->prepare($sql_g47);
        $sth->execute();
        my ($g47,$tch_seize_nho,$tch_call_req_nho);
        $sth->bind_columns(undef,\$g47,\$tch_seize_nho,\$tch_call_req_nho);
        $sth->fetch();
        if ($g47 < 98 ) {
                my $rand_suc = int(98400+rand(500))/100000;
                $tch_seize_nho = int($tch_call_req_nho*$rand_suc);
                my $sql_upbsc = qq { update tpa_unic_bts_sum set tch_seize_nho=$tch_seize_nho  where first_result='$f_hour' and ne_type=200 and ne_id=$bsc_id and sum_level=0 and sv_id=-1
                                  };
                print $bsc_id."         ".$g47."        ".$tch_seize_nho."\n";
                my $sth = $dbh->prepare($sql_upbsc);
                $sth->execute();
                $sth->finish();
                ++$count;
                        }
        $sth->finish();

        my $sql_g65 = qq{ select SFB_DIVFLOAT_1(drop_call_tch,tch_seize_nho,0,0)*100,drop_call_tch,tch_seize_nho from tpa_unic_bts_sum where first_result='$f_hour' and ne_id=$bsc_id and ne_type=200 and sv_id=-1 and sum_level=0 };
        my $sth = $dbh->prepare($sql_g65);
        $sth->execute();
        my ($g65,$drop_call_tch,$tch_seize_nho2);
        $sth->bind_columns(undef,\$g65,\$drop_call_tch,\$tch_seize_nho2);
        $sth->fetch();
        if ( $g65 > 0.7 ) {
                my $rand_suc = int(600 + rand(100))/100000;
                my $drop_call_tch = int($tch_seize_nho2 * $rand_suc);
                my $sql_upbsc = qq { update tpa_unic_bts_sum set drop_call_tch=$drop_call_tch where first_result='$f_hour' and ne_type=200 and ne_id=$bsc_id and sum_level=0 and sv_id=-1
                                  };
                print $bsc_id."         ".$g65."        ".$drop_call_tch."\n";
                my $sth = $dbh->prepare($sql_upbsc);
                $sth->execute();
                $sth->finish();
                ++$count;
                          }
       
        }
$sth->finish();

#==========Update region and provice's g47
my @reg_id=(-1744891292,-1458085134,-578147474, -402456014,  -75534827,   91461549,  202839087,  414603468,  867223656, 1478501655, 1948613501);
foreach my $reg_id (@reg_id)
        {
        my $sql_slreg = qq { select sum(tch_seize_nho),sum(drop_call_tch) from tpa_unic_bts_sum where first_result='$f_hour' and  sum_level=0 and sv_id=-1 and ne_type=200 and ne_id in (select ne_id from tcc_ne_snap where compress_date='$day' and ne_type=200 and region_id=$reg_id ) };
        my $sth = $dbh->prepare($sql_slreg);
        $sth->execute();
        my ($sum_reg_seiz,$sum_reg_drop);
        $sth->bind_columns(undef,\$sum_reg_seiz,\$sum_reg_drop);
        $sth->fetch();
        my $sql_upreg = qq { update tpa_unic_bts_sum set (tch_seize_nho,drop_call_tch) = ($sum_reg_seiz,$sum_reg_drop) where first_result='$f_hour' and sum_level=0 and sv_id=-1 and ne_id=$reg_id };
        my $sth = $dbh->prepare($sql_upreg);
        $sth->execute;
        $sth->finish();
        }

        my $sql_pro = qq { select sum(tch_seize_nho),sum(drop_call_tch) from tpa_unic_bts_sum where first_result='$f_hour' and  sum_level=0 and sv_id=-1 and ne_type=200 };
        my $sth = $dbh->prepare($sql_pro);
        $sth->execute();
        my ($sum_pro_seiz,$sum_pro_drop);
        $sth->bind_columns(undef,\$sum_pro_seiz,\$sum_pro_drop);
        $sth->fetch();
        my $sql_uppro = qq { update tpa_unic_bts_sum set (tch_seize_nho,drop_call_tch) = ($sum_pro_seiz,$sum_pro_drop) where first_result='$f_hour' and sum_level=0 and sv_id=-1 and ne_id=-1128953347 };
        my $sth = $dbh->prepare($sql_uppro);
        $sth->execute;
        $sth->finish();

print "You have update ".$count." BSC's data\n";

$dbh->disconnect();