#!/usr/bin/perl
###############################################################################
# Program : estimateScore.pl 计算评分卡的得分
# Scrpition: 执行本数据字段批量检查需要以下几个步骤
# 1.得到所有的评分卡以及评分卡下的所有的评分组。
# 2.根据评分组ID 得到所有的规则组信息
# 3.根据规则组信息,在check_result基础数据表中得到所有的规则组信息 每一个规则组有一个维度,不同的维度有不同的权重
# 4.从检查结果表check_result中得到规则组得分 insert into rulegroup_score
# 5.根据规则组得分表中的map_id 计算出 叶子节点的得分,插入到评分卡得分表 estimate_card_score
# 6.递归计算所有的节点的得分
# Writer : suhongyun
# Version :
#
# ------评分卡与评分组的关系的历史记录的维护,就要在表中添加日期的字段
# ------评分卡的频率的使用参照规则的频率
# ------
# ------
###############################################################################
use strict;#对语法做严格限制,如变量必须先用my声明
use DBI;
use Time::Local;
my $dbh;#与数据库的连接
my @Fieldrow;
# my $logFile;
# #日志文件目录
# #一个大于号,表示覆盖原有文件的内容
# #两个大于号表示在原有文件内容之后添加
# my $lfnowtime =GetNowDate();
# open $logFile,">checkLog$lfnowtime.txt";
# select $logFile;
#连接TD数据库
sub TDCconnect(){
$dbh = DBI->connect("dbi:ODBC:td_mds_VM_db", "", "") or "Can't connect to sample database: $DBI::errstr";
print "Connection is successful !!\n";
return $dbh;
}
#当前机构及其下属机构检查涉及的规则得分
sub calRuleGroupScore{
print("-----------------------------calRuleGroupScore begin-----------------------------\n");
my ($orgId,$rundate )= @_;
print("calRuleGroupScore para : $orgId & $rundate \n");
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 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]."',";
}
$orgPin = $orgPin."'".$orgInfo->[$#${orgInfo}][0]."'";
#1.通过日期和机构条件锁定数据范围,然后计算得到每条规则的得分情况
#2.关联规则表,得到规则的权重信息,进而求得规则的权重得分(ws)
#3.group by mapId,再sum(ws),求得规则组的得分情况
my $resultSQL =
"select map_id ,sum(rett) as rg_score from".
"(select main.map_id,main.check_id, ".
"CASE WHEN r.rule_weight = '0' THEN case when r.CheckDimension = '01' then 0.2
when r.CheckDimension ='05' then 0.2
when r.CheckDimension ='02' then 0.2
when r.CheckDimension ='03' then 0.2
when r.CheckDimension ='04' then 0.2
when r.CheckDimension ='06' then 0.2
else null end
ELSE r.rule_weight END as weight,score,score*weight as rett ".
"from ".
"(select map_id,check_id,cast(sum(passcount) as DEC(8,2))/sum(effect_count) as score ".
"from appdqc.check_result".
" where rundate=date'$rundate' and org_id in ($orgPin) group by map_id,check_id) main ".
"inner join appdqc.rule r on main.check_id = r.check_id) tt group by map_id" ;
my $ruleScoresth = $dbh->prepare($resultSQL) or return -1;
my $ret = $ruleScoresth->execute() or return -1;
#将每一个规则组的得分,按照机构,日期的维度进行存储
while (@Fieldrow = $ruleScoresth->fetchrow() ) { # Fieldrow一维数组
my $Map_Id = "$Fieldrow[0]";
my $score = "$Fieldrow[1]";
print("map_id: $Map_Id,score : $score \n");
my $insertGroupSQL = "insert into appdqc.rulegroup_score values(date'$rundate','$Map_Id','$orgId','$score')";
my $sth = $dbh->prepare($insertGroupSQL) or return -1;
my $ret = $sth->execute() or return -1;
$sth->finish();
}
$ruleScoresth->finish();
print("----------------------calRuleGroupScore End--------------------\n");
return 0;
}
sub calNodeScore{
print("--------------------------calNodeScore---------------------\n");
my ($curr_folder_id,$orgId,$rundate) = @_;#定义的变量需要用括号括起来,因为@_得到的是一个数组
my $ret;
my $broLength;
my $gs;
my $averageGs;
#得到当前评分卡下的节点(评分组)的信息
print("curr_folder_id:$curr_folder_id \n");
my $pfkSQL = "select folder_id,isleaf,weight from appdqc.estimate_group where status=1 and par_folder_id='$curr_folder_id'";
my $Nodesth = $dbh->prepare($pfkSQL) or return -1;
$broLength = $Nodesth->execute() or return -1; #broLength直接是结果集的长度
print("length : $broLength\n");
my $nodeCount;
if($broLength == 0){
print("$curr_folder_id no rulegroup or estimategroup");
}else{
while(my @Fieldrow = $Nodesth->fetchrow()){
my $child_folder_id = "$Fieldrow[0]";
my $isleaf = "$Fieldrow[1]";
#my $weight = "$Fieldrow[2]";
if("0" eq $isleaf ){# &&broLength eq 0 没有下级的节点是没有意义的
#当前节点的非叶子子节点的计算结果
my $ret = calNodeScore($child_folder_id,$orgId,$rundate);
$gs += $ret;
$averageGs = $gs;
}else{
# 当前节点的叶子子节点
print ("$child_folder_id is leafNode and broLength = $broLength\n");
# 结果集理论只有一条
# my $leafScoreSQL = "select * from appdqc.estimate_card_score ec inner join estimate_card on ec.folder_id=$child_folder_id "
# ."and ec.org_Id='3400008888' and and RunDate='20160530'";
my $leafScoreSQL = "select * from appdqc.estimate_card_score ec inner join estimate_group 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[6]";
print("leaf score :$s \n");
my $w = "$Fieldrow[5]";
$gs += $s ;
#$averageGs = ($gs/$broLength);
print("leaf score average : $gs \n");
$Leafsth ->finish();
}
}
$averageGs = ($gs/$broLength);
}
# print $logFile ("insert into estiamte_card_score $orgId $rundate $averageGs $curr_folder_id \n");
my $insertNodeScoreSQL = "insert into appdqc.estimate_card_score ".
"select '$orgId',date'$rundate',folder_id,par_folder_id,folder_name,'','$averageGs' 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();
#my $cardScoreinsert = "select sum(score/3) from estimate_card_score where par_folder_id= '$curr_folder_id'";
print("-------------------------------calNodeScore End----------------------\n");
#计算树形节点的得分,并插入到estimate_card_score树形节点得分信息表中
return $averageGs;
}
#计算评分卡值得入口
sub calCardscore{
print("--------------------------------calCardscore begin --------------------------\n");
my ($nodeId,$runDate,$orgId) = @_;
print(" calCardscore para :$nodeId,$runDate,$orgId \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_rulegroup_sql = "select er.map_id,er.weight,rgs.score from appdqc.estimate_mapid_relation er ".
" inner join rulegroup_score rgs on er.map_id = rgs.map_id " . "where group_Id='$groupId' ".
" and status = '1' ";
my $groupId_rulegroupsth = $dbh->prepare($groupId_rulegroup_sql) or return -1;
my $length = $groupId_rulegroupsth ->execute() or return -1;
my $gs = "0";
# 在评分组下的规则组的权重和得分加权求和之后得到叶子评分组的得分 (gs)
while(@Fieldrow = $groupId_rulegroupsth->fetchrow()){
my $s = "$Fieldrow[2]";
print(" score : $s \n");
my $w = "$Fieldrow[1]";
$gs += $s;
}
my $average = ($gs/$length);
$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,'','$average' from appdqc.estimate_group ".
" where folder_id='$groupId' and status = '1' ";
my $insertCardScoresth = $dbh->prepare($insertCardScoreSQL) or return -1;
$insertCardScoresth ->execute() or return -1;
$insertCardScoresth ->finish();
}
$cardGroupsqlSth->finish();
my $cardScore = calNodeScore($nodeId,$orgId,$runDate);#通过调用递归的计算节点的得分,最终求得评分卡最终的得分
my $cardScoreinsert = "insert into appdqc.estimate_card_score ".
"select '$orgId',date'$runDate',folder_id,par_folder_id,folder_name,'','$cardScore' from appdqc.estimate_card ".
" where folder_id='$nodeId'";
my $cardScoresth = $dbh->prepare($cardScoreinsert) or return -1;
$cardScoresth ->execute() or return -1;
$cardScoresth->finish();
print("-----------------------------calCardscore End------------------------\n");
}
sub main{
print "-----------------------------main begin----------------------------- ";
#得到数据库连接----TD库连接
TDconnect();
# if()
my $rundatesql = "select max(rundate) from appdqc.check_result ";
my $rundateSth = $dbh->prepare($rundatesql) or return -1;
$rundateSth ->execute() or return -1;
my @Fieldrow = $rundateSth->fetchrow();
#my $rundate = "$Fieldrow[0]";
$rundateSth ->finish();
my $rundate = "2016-06-01";
print("rundate : $rundate \n");
my $deleteHistory = "delete from appdqc.estimate_card_score where rundate = date'$rundate' ;".
"delete from appdqc.rulegroup_score where rundate = date'$rundate' ;";
my $deleteHistorySth = $dbh ->prepare($deleteHistory) or return -1;
$deleteHistorySth ->execute() or return -1;
$deleteHistorySth ->finish();
print("deleteHistory : fininsh \n");
#计算规则组得分,并插入到规则组得分信息表中
#传入参数:机构、日期,节点
#得到所有的农商行机构信息(包含总行机构),使用循环对每一个机构计算评分卡得分
my $getOrg = "select branch_id from bohz_appmds.tap_c_branch where branch_id in ( '3401020316','3401040438','3401030216','3400008888','3401010008')order by branch_id " ;
my $getOrgsth = $dbh->prepare($getOrg) or return -1;
$getOrgsth ->execute() or return -1;
while(my @Fieldrow = $getOrgsth->fetchrow()){
my $branchId = "$Fieldrow[0]";
calRuleGroupScore($branchId,$rundate);
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]";
# print("cardId : $cardId \n");
#递归计算树形节点的得分,并插入到estimate_card_score树形节点得分信息表中
calCardscore($cardId ,$rundate,$branchId);#参数为用户的输入节点ID
}
}
$getOrgsth ->finish();
print "-----------------------------main end -------------------------------";
}
#找到当前的日期,月份要加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);
相关推荐
代码中可能还涉及到了错误处理机制,用于捕获和报告无效的计算式。 总之,"cifa.rar_visual c"示例展示了如何利用Visual C++和自定义语法分析器处理包含数字、字母和预算运算的计算式。理解这个过程不仅可以帮助...
### 数据结构预算 #### 一、Java与面向对象程序设计 **1.1 Java语言基础知识** - **基本数据类型及运算** - 整型(`byte`, `short`, `int`, `long`) - 浮点型(`float`, `double`) - 字符型(`char`) - ...
在神经网络领域,TTA可以实现高速的矩阵运算,这是神经网络计算的基础,尤其是对于卷积神经网络(CNN)和递归神经网络(RNN)等复杂的模型。 网络游戏中的应用,例如角色识别、环境感知、敌我区分、玩家行为预测等...
如果关注的是滤波器的线性相位特性和稳定性,同时预算充足,FIR滤波器通常是更优的选择。如果追求经济性,并且滤波器的相位特性要求不是非常严格,或者应用场合对滤波器的稳定性要求不高,那么IIR滤波器可能更为合适...
每种物品不仅有自己的重量和价值,还有另一个维度的成本,需要在不超过背包容量和成本预算的前提下,使得总价值最大化。 #### 分组背包问题 物品被分成若干组,每组中只能选择一个物品放入背包。 #### 背包问题的...
”可能是指编写一个计算阶乘N的递归函数。递归算法在处理树结构、分治策略和回溯搜索等场景中非常有效,但需要注意的是,不适当的递归可能导致栈溢出。 ### 4. CMOS电路设计 CMOS(Complementary Metal-Oxide-...
以上列举了部分题目及其涉及的核心知识点,这些题目覆盖了数组、字符串、排序、循环、递归、贪心、DFS搜索等多种数据结构和算法。通过对这些问题的研究和实践,不仅可以提升个人算法能力,还能深入理解实际问题背后...
在实际工作中,我们可能遇到这样的情况:拥有两列数据,一列代表物品或服务的价格,另一列表示库存或可用数量,目标是找到一个组合,使得总价值最大化,同时不超过预算或限制。这种问题在优化理论中属于线性规划问题...
- 嵌套函数和递归:了解在函数内部定义函数以及函数自我调用的机制。 3. **面向对象编程** - 类与对象:理解类的概念,创建类的实例(对象),以及如何通过对象操作类的成员。 - 封装:利用访问修饰符(public、...
FinancialManagement1048聚焦于财务领域的决策问题,如投资组合优化、预算分配、成本控制等。这类问题通常需要建立数学模型,如线性规划、整数规划或非线性规划,来求解最优的财务策略。解决此类问题的关键在于理解...
- **解题思路**:可以通过递归或循环的方式实现,递归方式更直观但可能导致栈溢出,循环方式更加稳定。关键在于确定每个步骤的操作。 - **代码示例**: ```c #include int func(unsigned int n) { if (n == 1) ...
- **规划**:制定详细计划,包括时间表、预算等。 - **执行**:实施项目计划。 - **监控**:跟踪进度,解决问题。 - **收尾**:完成项目交付物,评估项目成果。 #### 框架篇 ##### Struts1的运行原理 - **Action...