得分计算最新

#!/usr/bin/perl

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

# Program : estimateScore.pl 计算评分卡的得分

# Scrpition: 执行本数据字段批量检查需要以下几个步骤

# Version :

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

use strict;#对语法做严格限制，如变量必须先用my声明

use DBI;

use Time::Local;

 

my $dbh;#与数据库的连接

my @Fieldrow;

# my $logFile;

# #日志文件目录

# #一个大于号，表示覆盖原有文件的内容

# #两个大于号表示在原有文件内容之后添加

# my $lfnowtime =GetNowTime();

# open $logFile,">checkLog$lfnowtime.txt";

# select $logFile;

 

 

#连接TD数据库

sub TDconnect(){

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

print "Connection is successful !!\n";

return $dbh;

}

 

 

#当前机构及其下属机构检查涉及的规则得分

sub calRuleScore{

print("-----------------------------calRuleScore begin-----------------------------\n");

my ($cardId,$rundate,$orgId)= @_; 

print("current org_id : $orgId  \n");

 

my $resultSQL = "";

#总行需要查看全行的情况，无需机构拼接

#关联rule,得到当前计算的评分卡关联的规则检查结果

if($orgId eq "3400008888"){

 

$resultSQL = 

"select main.check_id, ecr.weight,main.pass_rate,main.pass_rate*ecr.weight*100 as score ".

"from ".

"(select check_id,cast(sum(passcount) as DEC(16,4))/sum(effect_count) as pass_rate ".

"from appdqc.check_result_all where rundate=date'$rundate'  group by check_id) main ".

"inner join appdqc.estimate_checkid_relation ecr on main.check_id = ecr.check_id ".

"inner join appdqc.rule r on main.check_id = r.check_id and r.card_id='$cardId'";

}else{

 

my $orgInfoSQL = " WITH  recursive temporg(id,name,pid,leveld) as( ".

" select a.branch_id,a.branch_name,a.par_branch_id,0 from  bohz_appmds.tap_c_branch_all  a where branch_id = '$orgId' union all ".

"select a.branch_id,a.branch_name,a.par_branch_id,c.leveld+1 from bohz_appmds.tap_c_branch_all a ,temporg c ".

"where  c.id = a.par_branch_id and a.branch_name<>'' and leveld<7)".

"select id from temporg";

#my $sth = $dbh->prepare($orgInfoSQL) or return -1;

#my $ret = $sth->execute() or return -1;  

#my $orgInfo = $sth->fetchall_arrayref();

#$sth->finish();

 

my $orgPin="" ;

#for(my $i = 0 ;$i<$#${orgInfo};$i++){

#$orgPin = $orgPin."'".$orgInfo->[$i][0]."',";

#}

#for得出的结果是['3400','3455',],所以要除去最后一个逗号，或者拼接一个其他字符串，是的符合in条件的数据语法

$orgPin = $orgPin."''";

 

$resultSQL = 

"select main.check_id, ecr.weight,main.pass_rate,main.pass_rate*ecr.weight*100 as score ".

"from ".

"(select check_id,cast(sum(passcount) as DEC(8,2))/sum(effect_count) as pass_rate ".

"from appdqc.check_result_all where rundate=date'$rundate' and org_id ='$orgId' group by check_id) main ".

"inner join appdqc.estimate_checkid_relation ecr on main.check_id = ecr.check_id ".

"inner join appdqc.rule r on main.check_id = r.check_id and r.card_id='$cardId'";

}

 

my $ruleScoresth = $dbh->prepare($resultSQL) or return -1;

my $ret = $ruleScoresth->execute() or return -1;  

 

#将每一个规则的得分，按照机构，日期的维度进行存储

while (@Fieldrow = $ruleScoresth->fetchrow() ) { # Fieldrow一维数组

 

my $check_id = "$Fieldrow[0]"; 

my $score = "$Fieldrow[3]";

#print("check_id: $check_id,org_id: $orgId,score : $score  \n");

my $insertGroupSQL = "insert into appdqc.rule_score values(date'$rundate','$check_id','$orgId','$score','$cardId')";

my $sth = $dbh->prepare($insertGroupSQL) or  return -1;

my $ret = $sth->execute() or return -1;  

$sth->finish();

}

$ruleScoresth->finish();

print("-----------------------------calRuleScore   end -----------------------------\n");

return 0;

 

}

 

sub calLeafNodeScore{

my ($nodeId,$runDate,$orgId) = @_;

print("--------------------------------calLeafNodeScore begin --------------------------\n");

#递归计算评分卡下所有的叶子评分组

my $cardGroupsql = "WITH  recursive temptab(id,name,pid,isleaf,leveld) as(".

" select a.folder_id,a.folder_name,a.par_folder_id,a.isleaf,0 from appdqc.estimate_group a where par_folder_id ='$nodeId' union all ".

" select a.folder_id,a.folder_name,a.par_folder_id,a.isleaf,c.leveld+1 from appdqc.estimate_group a ,temptab c".

" where  c.id = a.par_folder_id and status='1' and leveld<7)".

" select id from temptab where isleaf = '1' ";

my $cardGroupsqlSth = $dbh->prepare($cardGroupsql) or return -1;

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

 

while(my @Fieldrow = $cardGroupsqlSth->fetchrow()){

my $groupId = "$Fieldrow[0]";

my $gs = "0";

my $groupId_rule_sql = "select er.check_id,er.weight*100,rs.score from appdqc.estimate_checkid_relation er ".

" inner join rule_score rs on er.check_id = rs.check_id  " . "where folder_id='$groupId' ".

" and status = '1' and org_id = '$orgId' and rundate = date'$runDate'"; 

my $groupId_rulegroupsth = $dbh->prepare($groupId_rule_sql) or return -1;

my $length = $groupId_rulegroupsth ->execute() or return -1;

 

my $maxScore="0";

my $gs = "0";

# 在评分组下的规则的权重和得分加权求和之后得到叶子评分组的得分

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

my $s = "$Fieldrow[2]";

my $max = "$Fieldrow[1]";

$gs += $s;

$maxScore += $max;

}

$groupId_rulegroupsth ->finish();

# print(" folder_id : $groupId ,average : $average ");

#将每一个叶子节点的得分插入到estimate_card_score 节点得分表中,变量中不加单引号，会出现插入数据表中的数据结尾出现‘.’

# estiamte_card_score 联合唯一主键  orgId rundate folder_id

my $insertCardScoreSQL  = "insert into appdqc.estimate_card_score ".

"select '$orgId',date'$runDate',folder_id,par_folder_id,folder_name,'$maxScore','$gs' from appdqc.estimate_group ".

" where folder_id='$groupId' and status = '1' ";

# print "$insertCardScoreSQL \n";

my $insertCardScoresth = $dbh->prepare($insertCardScoreSQL) or return -1;

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

$insertCardScoresth ->finish();

 

}

$cardGroupsqlSth->finish();

print("--------------------------------calLeafNodeScore end  --------------------------\n");

}

 

sub calNodeScore{

print("-----------------------------calNodeScore  begin-----------------------------\n");

my ($curr_folder_id,$orgId,$rundate) = @_;#定义的变量需要用括号括起来，因为@_得到的是一个数组

my $currNodeScore;

my $maxScore;

print("curr_folder_id:$curr_folder_id \n");

 

#计算当前节点的得分（需要递归计算所有下级的得分）

my $pfkSQL = "select folder_id,isleaf from appdqc.estimate_group where status=1 and par_folder_id='$curr_folder_id'";

my $Nodesth = $dbh->prepare($pfkSQL) or  return -1;

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

while(my @Fieldrow = $Nodesth->fetchrow()){

my $child_folder_id = "$Fieldrow[0]";

my $isleaf = "$Fieldrow[1]";

if("0" eq $isleaf ){

#当前节点的非叶子子节点的计算需要继续递归

my $scoreAndMaxScore = calNodeScore($child_folder_id,$orgId,$rundate);

my @childNodeScoreArr = split("#",$scoreAndMaxScore);

my $childNodeScore = @childNodeScoreArr[0];

my $childNodeMaxScore = @childNodeScoreArr[1];

$currNodeScore += $childNodeScore;

$maxScore += $childNodeMaxScore;

}else{

# 当前节点的叶子子节点

my $leafScoreSQL = " select ec.score,ec.maxscore from appdqc.estimate_card_score ec ".

  " inner join estimate_group eg on ec.folder_id='$child_folder_id' ".

  " and ec.org_Id='$orgId' and rundate = date'$rundate' ";

 

my $Leafsth = $dbh->prepare($leafScoreSQL) or  return -1;

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

my @Fieldrow = $Leafsth->fetchrow();

my $s = "$Fieldrow[0]";

my $ms = "$Fieldrow[1]";

print("leaf node: $child_folder_id ,score :$s  \n");

$currNodeScore += $s ;

$maxScore += $ms;

$Leafsth ->finish();

}

}

my $insertNodeScoreSQL = "insert into appdqc.estimate_card_score  ".

"select '$orgId',date'$rundate',folder_id,par_folder_id,folder_name,'$maxScore','$currNodeScore' from appdqc.estimate_group  ".

" where folder_id = $curr_folder_id";

my $insertNodesth = $dbh->prepare($insertNodeScoreSQL) or return -1;

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

$insertNodesth ->finish();

$Nodesth->finish();

 

print("-------------------------------calNodeScore  End  ---------------------------\n");

return $currNodeScore."#".$maxScore;

}

 

#计算评分卡值得入口

sub calCardscore{

print("--------------------------------calCardscore begin --------------------------\n");

my ($nodeId,$runDate,$orgId) = @_;

my $cardScoreStr = calNodeScore($nodeId,$orgId,$runDate);#通过调用递归的计算节点的得分，最终求得评分卡最终的得分

my @cardScoreArr = split("#",$cardScoreStr);

my $cardScore = @cardScoreArr[0];

my $cardScoreMax = @cardScoreArr[1];

 

my $cardScoreinsert = "insert into appdqc.estimate_card_score ".

"select '$orgId',date'$runDate',folder_id,par_folder_id,folder_name,'$cardScoreMax','$cardScore' from appdqc.estimate_card ".

" where folder_id='$nodeId' and status='1' ";

 

my $cardScoresth = $dbh->prepare($cardScoreinsert) or return -1;

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

$cardScoresth->finish(); 

 

print("--------------------------------calCardscore end   --------------------------\n");

 

}

 

sub main{

print (GetNowTime()."--------------------------------------------main begin--------------------------------------------\n ");

#得到数据库连接----TD库连接

TDconnect();

 

#默认计算规则检查结果表最新检查日期的评分结果

my $rundatesql = "select max(rundate) from appdqc.check_result_all ";

my $rundateSth = $dbh->prepare($rundatesql) or return -1;

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

my @Fieldrow = $rundateSth->fetchrow();

my $rundate = "$Fieldrow[0]";

$rundateSth ->finish();

 

#如果有传入日期参数，以传进来的日期参数为准，从而实现手工调度或者按需调度

if($#ARGV==0){

$rundate = $ARGV[0];

}

 

my $deleteHistory  = "delete from appdqc.estimate_card_score where rundate = date'$rundate' ;".

"delete from appdqc.rule_score where rundate = date'$rundate' ;";

 

my $deleteHistorySth = $dbh ->prepare($deleteHistory) or return -1;

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

$deleteHistorySth ->finish();

print("deleteHistory : succeed  \n");

print("rundate : $rundate  \n");

 

my $cardInfo = "select folder_id from appdqc.estimate_card where status = '1' ";

my $cardInfoSth = $dbh ->prepare($cardInfo) or return -1;

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

while(my @Fieldrow = $cardInfoSth->fetchrow()){

my $cardId = "$Fieldrow[0]";

#结果表里可能包括所有的机构，包括四级机构，但是实际计算时只需到分支行

#规则检查结果的最新表，有机构存在才计算对应的得分

my $getOrg = "select distinct org_id from appdqc.check_result_all inner join bohz_appmds.tap_c_branch_all".

" on org_id = branch_id and branch_level<=3 ".

"where rundate= date '$rundate'";

 

my $getOrgsth = $dbh->prepare($getOrg) or return -1;

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

while(my @Fieldrow = $getOrgsth->fetchrow()){

my $branchId = "$Fieldrow[0]";

#递归计算树形节点的得分，并插入到estimate_card_score树形节点得分信息表中

calRuleScore($cardId,$rundate,$branchId);

calLeafNodeScore($cardId ,$rundate,$branchId);

calCardscore($cardId ,$rundate,$branchId);#参数为用户的输入节点ID

}

$getOrgsth ->finish();

}

print (GetNowTime()."--------------------------------------------main end --------------------------------------------\n ");

}

 

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 = GetNowDate()." ".$hour.":".$min.":".$sec;

return $stime;

}

 

#找到当前的日期，月份要加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;

}

 

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

exit($rc);