递归查询-操作excel

#!/usr/bin/perl

###############################################################################

# zengst 2015-12-01

# 功能描述：将DB2规则检测明细按照二级机构生成明细文件

# 一个机构的下级数据需要通过机构的层级关系来递归获得

###############################################################################

use strict;

use DBI;

use Time::Local;

use File::Copy;

 

my $appmds="bohz_appmds";

my $TDCon;

my $DB2Con;

my $checkResult = 'EASTDQM.Check_Result';#轻度结果汇总表

my $checkDetail = 'EASTDQM.Check_Result_Detail';

my $ruleInfo='EASTDQM.rule';

my $RUNDATE=GetNowDate();#质量检查日期

 

#找到当前的日期，月份要加1，年份是从1900开始算的

sub GetNowDate{

my ($sec, $min, $hour, $day, $mon, $year, $wday, $yday, $isdst) = localtime(); 

$mon = sprintf("%02d", $mon+1);

$day = sprintf("%02d", $day);

my $stime = ($year+1900)."-".$mon."-".$day;

return $stime;

}

 

#得到当前时间

sub GetNowTime{

my ($sec, $min, $hour, $day, $mon, $year, $wday, $yday, $isdst) = localtime(); 

$hour = sprintf("%02d", $hour);

$min  = sprintf("%02d", $min);

$sec  = sprintf("%02d", $sec);

my $stime = $hour.":".$min.":".$sec;

return $stime;

}

#得到数据库链接

sub GetTDconnect(){

$TDCon = DBI->connect("dbi:ODBC:1.104", "dbc", "dbc") or "Can't connect to sample database: $DBI::errstr";

print "Connection is successful !!\n";

return $TDCon;

}

#得到DB2的连接

sub GetDB2connect(){

$DB2Con = DBI->connect("dbi:ODBC:ahnxods", "odsuser", "ytods") or "Can't connect to sample database: $DBI::errstr";

print "Connection is successful !!\n";

return $DB2Con;

}

 

 

#函数：Main

#主函数，用来循环记录集，并计算每一个字段

sub main

{

 

print (GetNowTime() . "......perl starting......:\n");

 

#先校验对数据库的联通是否都成功

$TDCon = GetTDconnect();

unless( defined($TDCon) ){

print (GetNowTime() . " Failed to execute GetTDconnect()\n");

return 1;

}

print (GetNowTime() . " GetTDconnect:Succeed\n");

$DB2Con = GetDB2connect();

unless( defined($DB2Con) ){

print (GetNowTime() . " Failed to execute GetDB2connect()\n");

return 1;

}

print (GetNowTime() . " GetDB2connect:Succeed\n");

 

 

#获得所有二级机构(农商行级别)的机构代码

my $level2OrgSQL = "select branch_id,branch_name from ${appmds}.tap_c_branch where branch_level=2 "

."and '${RUNDATE}' between start_dt and end_dt order by branch_id ";

my $TDsth = $TDCon->prepare($level2OrgSQL) or return -1;

$TDsth->execute() or return -1;

my ${org_Info} = $TDsth->fetchall_arrayref();

$TDsth->finish();

 

#每个农商行的机构及其下属机构的问题明细保存为一个文件

for my $i(0..$#${org_Info}){

 

my @currOrgAndBelow=();

 

my $currOrg=${org_Info}->[$i]->[0];

 

#$currOrg=s/^\s+|\s+$//g ;除去首尾空格

print "**************current org: $currOrg*************** \n";

 

#递归获得每一个二级机构的所有下级

my $belowLevelSQL = 

"WITH RECURSIVE cte(id,name,pid) as(".

"select a.branch_id,a.branch_name,a.par_branch_id from ${appmds}.tap_c_branch a where branch_id='${currOrg}' union all ".

"select a.branch_id,a.branch_name,a.par_branch_id   from ${appmds}.tap_c_branch a inner join cte c on c.id = a.par_branch_id)".

"select id,name,pid from cte order by pid";

my $belowLevelsth = $TDCon->prepare($belowLevelSQL) or return -1;

$belowLevelsth->execute() or return -1;

 

my @Fieldrow;

my $branchId;

 

while ( @Fieldrow = $belowLevelsth->fetchrow() ) {

 

$branchId = "$Fieldrow[0]";

   #$branchName = "$Fieldrow[1]";

print "$branchId,$branchName\n";

 

#存储当前机构及其下属机构

push(@currOrgAndBelow,$branchId);

}

 

my $retStr = getInCondition(@currOrgAndBelow);

$belowLevelsth->finish();

 

#连接到远程DB2的环境，查询问题明细。问题明细表太大，不易导入到本地

#需要根据规则的导出标志设置来关联和校验信息

my $issueDetailSQL = "select * from  ${checkDetail} d ".

" left join ${ruleInfo} r on d.check_id=r.check_Id".

"where rundate='${RUNDATE}' and r.isexport='Y' and org_id in $retStr ";

 

my $issueDetailsth = $DB2Con->prepare($issueDetailSQL) or return -1;

$issueDetailsth->execute() or return -1;

 

my $fileName = "D:/detail_".${currOrg}.".xls"

my $xls = Spreadsheet::WriteExcel::Big->new( "$fileName" );

$xls->compatibility_mode();

my $xlsSheet= $xls->add_worksheet( "$filename" );

my $titleStyle  = $xls->add_format(size => 10,bold    => 1,border  => 2,align   => 'center');

my $rowStyle    = $xls->add_format(align   => 'left',border  => 1);

 

my $row = 0;

 

$xlsSheet->write( $row, 0, decode("gbk","检查编号") ,$titleStyle );

$xlsSheet->write( $row, 1, decode("gbk","检查运行日期") ,$titleStyle );

$xlsSheet->write( $row, 2, decode("gbk","系统代码") ,$titleStyle );

$xlsSheet->write( $row, 3, decode("gbk","表名") ,$titleStyle );

$xlsSheet->write( $row, 4, decode("gbk","字段名") ,$titleStyle );

$xlsSheet->write( $row, 5, decode("gbk","主键字段值") ,$titleStyle );

$xlsSheet->write( $row, 6, decode("gbk","字段值") ,$titleStyle );

 

my @detailRow;

while ( @detailRow = $issueDetailsth->fetchrow() ) {

 

$row ++;

$xlsSheet->write( $row, 0, decode("gbk",$detailRow[0]) ,$rowStyle );

$xlsSheet->write( $row, 1, decode("gbk",$detailRow[1]) ,$rowStyle );

$xlsSheet->write( $row, 2, decode("gbk",$detailRow[2]) ,$rowStyle );

$xlsSheet->write( $row, 3, decode("gbk",$detailRow[3]) ,$rowStyle );

$xlsSheet->write( $row, 4, decode("gbk",$detailRow[4]) ,$rowStyle );

$xlsSheet->write( $row, 5, decode("gbk",$detailRow[5]) ,$rowStyle );

$xlsSheet->write( $row, 6, decode("gbk",$detailRow[6]) ,$rowStyle );

 

}

$xls->close();

 

$issueDetailsth->finish();

 

 

print "**********************end******************\n";

}#for org block

 

 

return 0 ;

 

}#main block

 

my $rc = main();#函数的入口

 

#接收数组字符串，转换成sql查询的in的字符条件

sub getInCondition{

 

my @charArr=@_;

#数组的最大下标

my $arrLeng = $#charArr;

my $retStr = "('";

#这里是下标的循环

foreach my $i(0..$arrLeng){

if($i == $arrLeng){

$retStr = $retStr.@charArr->[$i]."')";

}else{

$retStr = $retStr.@charArr->[$i]."','";

}

}

 

return $retStr;

}

 

exit($rc);