最好能写出详细的方法，我现在能够实现的是用.properties连接数据库 想转换成java版本，打成jar包放到linux环境的定时任务执行
my ($sum_level,$sql,$dbhome,$gsm_type,$lte_type,$start_time,$end_time,$db_type,$scm,$programe,$start_date,$end_date,$db_ini,$detials_time,$detiale_time,$ustime);
my ($cmd_line,$app_name,$province_id,$Convert_type,$debug,$detail_log_name,$flag,$range_second,$todaysum);
my $dbhome = $ENV{"DB_PATH"};
if((!defined($dbhome) || ! -d $dbhome))
{
die("Do not define the env 'DB_PATH' or it does not exist\n");
}
my $cmd_line = $0." ".join(" ",@ARGV);
my $programe = $0;
$programe =~ /\b(\w+).pl/i;
my $app_name = $1;
my $db_ini = "$dbhome/db.ini";
my $gsm_type = "GSM";
my $lte_type = "LTE";
GetOptions(
"u=i" =>\$sum_level,
"s:s" =>\$start_time,
"e:s" =>\$end_time
);
my $scm = new SumCommonModule("$app_name",$debug,$detail_log_name,$$);
$scm->connect_db($db_ini);
my $db_type = $scm->get_db_type();
if($sum_level ==1 )
{
$start_time = int2date(date2int(current()) - 24*3600) if($start_time eq "");
$end_time = $start_time if($end_time eq "");
}
elsif($sum_level ==3 )
{
$start_time = int2date(date2int(current()) - 24*28*3600) if($start_time eq ""); #��Ҫ�Զ����㴦
$end_time = $start_time if($end_time eq "");
}
else
{
die("Please input sum_level Parameter ,The sum_level must be in(1,3) \n");
}
($start_time,$end_time) = time_range($start_time,$end_time,$sum_level,"T");
($detials_time,$detiale_time) = time_range($start_time,$end_time,3,"D");
my ($start_date,$end_date) = time_range($start_time,$end_time,$sum_level,"D");
$ustime = TimeOpt::GMtime($start_time,"-1D");
$ustime = TimeOpt::CFtime("MM/DD/YYYY",$ustime);
print "xxxxxxxxx $sum_level \n";
print "$start_time,$end_time,$detials_time,$detiale_time,$ustime\n";
#<=============================================================================
my ($tmp_irms_radio2g_day,$tmp_irms_radio4g_day,$tmp_sitecode_num,$tmp_irms2npm_stcode_traffic,$tmp_last_num); #������ʱ��
create_temp_name("tmp",\$tmp_irms_radio2g_day,\$tmp_irms_radio4g_day,\$tmp_sitecode_num,\$tmp_irms2npm_stcode_traffic,\$tmp_last_num);
#<=============================================================================
print "xxxxxxxxxxxxxxxb $db_type \n";
$scm->set_sql_note(" Delete irms_radio_cell double cell_name");
$sql = qq {
DELETE nrmdb:irms_radio_cell
WHERE label_cn in(
SELECT a.label_cn
FROM nrmdb:irms_radio_cell a,nrmdb:irms_radio_cell b
WHERE a.label_cn = b.label_cn
AND a.cuid != b.cuid);
DELETE irms2npm_stcode_traffic
WHERE first_result = '$start_date';
insert into irms2npm_stcode_traffic (first_result, sitecode, codename)
select '$start_date' , mobile_sitecode, report_site_code_name
from nrmdb:irms_sitecode_cell
where 1=1;
};
$scm->execute_batch($sql);
GSM_cell_sum();
LTE_cell_sum();
my $pattern = "[-\/ :]+";
my $test_date = TimeOpt::CFtime("YYYY-MM-DD "."00:00:00",$start_time);
my @splite_list= split(/$pattern/, $test_date);
my ($syear,$smon,$sday,$shour,$smin,$ssec) = @splite_list; # MM/DD/YYYY �ж��Ƿ���1�ţ�ִ���»���
if ($sday == 1)
{
Month_sum( TimeOpt::GMtime($test_date,"-1M") , TimeOpt::GMtime($test_date,"-1D"));
}
sub GSM_cell_sum()
{
$scm->set_sql_note("Start $gsm_type cell_sum");
my $sql = qq
{
CREATE TABLE $tmp_irms_radio2g_day
(
int_id integer,
first_result datetime year to second,
label_cn varchar(255),
tch_traffic float
);
CREATE INDEX ix0_irms_radio2g_day on $tmp_irms_radio2g_day (int_id,label_cn) ;
INSERT INTO $tmp_irms_radio2g_day (int_id ,first_result,tch_traffic)
SELECT ne_id,first_result,tch_traffic
FROM tpa_radio_sum
WHERE first_result='$start_time'
AND sum_level=1
AND ne_type=300
AND busy_type=0;
UPDATE $tmp_irms_radio2g_day a
SET label_cn= (
SELECT userlabel
FROM tcc_ne_frame b
WHERE a.int_id = b.ne_id
AND b.ne_type=300
AND b.confirmed not in(2,5)
)
WHERE EXISTS(
SELECT 1
FROM tcc_ne_frame b
WHERE a.int_id = b.ne_id
AND b.ne_type=300
AND b.confirmed not in(2,5)
);
SELECT sitecode.sitecode, cell.first_result, sitecode.network_type, sum( cell.tch_traffic) traffic
FROM nrmdb:irms_radio_cell sitecode , $tmp_irms_radio2g_day cell
WHERE sitecode.label_cn = cell.label_cn
AND sitecode.network_type = '$gsm_type'
GROUP BY 1,2,3
INTO TEMP $tmp_irms2npm_stcode_traffic with no log;
create index ix2_tmp_irms2npm_stcode_traffic on $tmp_irms2npm_stcode_traffic (sitecode);
UPDATE irms2npm_stcode_traffic a set tch_traffic=
(
SELECT traffic
FROM $tmp_irms2npm_stcode_traffic b
WHERE a.sitecode = b.sitecode
AND a.first_result = '$start_date'
AND b.network_type = '$gsm_type'
)
WHERE EXISTS
(
SELECT 1
FROM $tmp_irms2npm_stcode_traffic b
WHERE a.sitecode = b.sitecode
AND a.first_result = '$start_date'
AND b.network_type = '$gsm_type'
);
SELECT sitecode,sum(tch_traffic) num
FROM irms2npm_stcode_traffic
WHERE first_result between '$detials_time' and '$start_date'
GROUP BY 1
INTO temp $tmp_sitecode_num with no log ;
CREATE INDEX ix0_$tmp_sitecode_num on $tmp_sitecode_num( sitecode);
UPDATE irms2npm_stcode_traffic a
SET last_traffic = (
SELECT num
FROM $tmp_sitecode_num b
WHERE a.sitecode = b.sitecode
AND a.first_result = '$start_date'
)
WHERE EXISTS (
SELECT 1
FROM $tmp_sitecode_num b
WHERE a.sitecode = b.sitecode
AND a.first_result = '$start_date'
);
SELECT sitecode,traffic
FROM irms2npm_stcode_traffic
WHERE first_result = '$detials_time'
INTO temp $tmp_last_num with no log ;
CREATE INDEX ix0_$tmp_last_num on $tmp_last_num( sitecode);
UPDATE irms2npm_stcode_traffic a
SET traffic = (
SELECT traffic
FROM $tmp_last_num b
WHERE a.sitecode = b.sitecode
AND a.first_result = '$start_date'
)
WHERE EXISTS (
SELECT 1
FROM $tmp_last_num b
WHERE a.sitecode = b.sitecode
AND a.first_result = '$start_date'
);
};
$scm->set_sql_note(" Summary $gsm_type sitecode!");
$scm->execute_batch($sql);
$scm->drop_temp_table($tmp_irms_radio2g_day,$tmp_sitecode_num,$tmp_irms2npm_stcode_traffic,$tmp_last_num);
}
sub LTE_cell_sum()
{
$scm->set_sql_note("Start $lte_type cell_sum");
my $sql = qq
{
CREATE TABLE $tmp_irms_radio4g_day
(
int_id integer,
first_result datetime year to second,
label_cn varchar(255),
upoct float
);
CREATE INDEX ix0_irms_radio2g_day on $tmp_irms_radio4g_day (int_id,label_cn) ;
INSERT INTO $tmp_irms_radio4g_day (int_id ,first_result,upoct)
SELECT ne_id,first_result,up_oct_ul+up_oct_dl
FROM tpa_eutrancell_q_sum
WHERE first_result='$start_time'
AND sum_level=1
AND ne_type=8105
AND busy_type=0;
UPDATE $tmp_irms_radio4g_day a
SET label_cn= (
SELECT userlabel
FROM tcc_ne_frame b
WHERE a.int_id = b.ne_id
AND b.ne_type=8105
AND b.confirmed not in(2,5)
)
WHERE EXISTS(
SELECT 1
FROM tcc_ne_frame b
WHERE a.int_id = b.ne_id
AND b.ne_type=8105
AND b.confirmed not in(2,5)
);
SELECT sitecode.sitecode, cell.first_result, sitecode.network_type, sum( cell.upoct) traffic
FROM nrmdb:irms_radio_cell sitecode , $tmp_irms_radio4g_day cell
WHERE sitecode.label_cn = cell.label_cn
AND sitecode.network_type = '$lte_type'
GROUP BY 1,2,3
INTO TEMP $tmp_irms2npm_stcode_traffic with no log;
CREATE INDEX ix2_tmp_irms2npm_stcode_traffic on $tmp_irms2npm_stcode_traffic (sitecode);
UPDATE irms2npm_stcode_traffic a set upoct=
(
SELECT traffic
FROM $tmp_irms2npm_stcode_traffic b
WHERE a.sitecode = b.sitecode
and a.first_result = '$start_date'
and b.network_type = '$lte_type'
)
WHERE EXISTS
(
SELECT 1
FROM $tmp_irms2npm_stcode_traffic b
WHERE a.sitecode = b.sitecode
and a.first_result = '$start_date'
and b.network_type = '$lte_type'
);
SELECT sitecode,sum(upoct) num
FROM irms2npm_stcode_traffic
WHERE first_result between '$detials_time' and '$start_date'
GROUP BY 1
INTO temp $tmp_sitecode_num with no log ;
CREATE INDEX ix0_$tmp_sitecode_num on $tmp_sitecode_num( sitecode);
UPDATE irms2npm_stcode_traffic a
SET last_oct = (
SELECT num
FROM $tmp_sitecode_num b
WHERE a.sitecode = b.sitecode
AND a.first_result = '$start_date'
)
WHERE EXISTS (
SELECT 1
FROM $tmp_sitecode_num b
WHERE a.sitecode = b.sitecode
AND a.first_result = '$start_date'
);
SELECT sitecode,oct
FROM irms2npm_stcode_traffic
WHERE first_result = '$detials_time'
INTO temp $tmp_last_num with no log ;
CREATE INDEX ix0_$tmp_last_num on $tmp_last_num( sitecode);
UPDATE irms2npm_stcode_traffic a
SET oct = (
SELECT oct
FROM $tmp_last_num b
WHERE a.sitecode = b.sitecode
AND a.first_result = '$start_date'
)
WHERE EXISTS (
SELECT 1
FROM $tmp_last_num b
WHERE a.sitecode = b.sitecode
AND a.first_result = '$start_date'
);
};
$scm->set_sql_note(" Summary $lte_type sitecode!");
$scm->execute_batch($sql);
$scm->drop_temp_table($tmp_irms_radio4g_day,$tmp_sitecode_num,$tmp_irms2npm_stcode_traffic,$tmp_last_num);
}
sub Month_sum
{
my $st = shift;
my $et = shift;
$st = TimeOpt::CFtime("MM/DD/YYYY",$st);
$et = TimeOpt::CFtime("MM/DD/YYYY",$et);
$scm->set_sql_note("Start Month_sum cell_sum");
my $sql = qq
{
SELECT sitecode,sum(upoct) lte_num,sum(tch_traffic) gsm_num
FROM irms2npm_stcode_traffic
WHERE first_result between '$st' and '$et'
GROUP BY 1
INTO temp $tmp_sitecode_num with no log ;
CREATE INDEX ix0_$tmp_sitecode_num on $tmp_sitecode_num( sitecode);
UPDATE irms2npm_stcode_traffic a
SET oct = (
SELECT lte_num
FROM $tmp_sitecode_num b
WHERE a.sitecode = b.sitecode
AND a.first_result = '$start_date'
)
WHERE EXISTS (
SELECT 1
FROM $tmp_sitecode_num b
WHERE a.sitecode = b.sitecode
AND a.first_result = '$start_date'
);
UPDATE irms2npm_stcode_traffic a
SET traffic = (
SELECT gsm_num
FROM $tmp_sitecode_num b
WHERE a.sitecode = b.sitecode
AND a.first_result = '$start_date'
)
WHERE EXISTS (
SELECT 1
FROM $tmp_sitecode_num b
WHERE a.sitecode = b.sitecode
AND a.first_result = '$start_date'
);
};
$scm->execute_batch($sql);
$scm->drop_temp_table($tmp_sitecode_num);
}
$scm->end();
