`
zengshaotao
  • 浏览: 788926 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

递归均分实现

 
阅读更多

总体功能:

 

假定A下有A1,A2,A3,A4.并且A1到A4的各个节点都有子节点,

当设置A1,A2的分配权重和为R,那么A3,A4会均分剩下的 1-R

 

同时会递归均分计算A1到A4每个节点的子节点,直至层级关系的最后

 

package com.teradata.estimateCardManage.dao;

 

import java.math.BigDecimal;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import java.util.Map;

 

import org.apache.commons.lang.StringUtils;

 

import com.teradata.bos.dqc.dao.Dao;

import com.teradata.bos.dqc.dao.DaoImp;

import com.teradata.util.CommonFunc;

import com.teradata.util.Constants;

 

public class EstimateGroupManageDAO {

 

 

public List estimateGroupInitAndQuery(Map<String, Object> paramsMap){

 

List childrenList=null;

Dao dao = new DaoImp();

try{

StringBuffer sb = new StringBuffer();

sb.append("select folder_id , folder_name,folder_desc,par_folder_id as parId,")

.append("weight,create_oper,start_dt,dis_weight ")

.append("from appdqc.estimate_group ")

.append(" where status='1' and ")

.append(" par_folder_id='"+paramsMap.get("parId")+"'");

 

String whereClause = "";

 

if(!StringUtils.isBlank((String)paramsMap.get("groupName"))){

whereClause = whereClause+" and FOLDER_NAME like '%"+paramsMap.get("groupName")+"%'";

}

if(!StringUtils.isBlank((String)paramsMap.get("groupID"))){

whereClause = whereClause+" and FOLDER_ID = '"+paramsMap.get("groupID")+"'" ;

}

 

sb.append(whereClause);

childrenList=dao.getResultList(sb.toString());

 

}catch(Exception e){

e.printStackTrace();

}

return childrenList;

}

 

public List queryCardScore(Map<String, Object> paramsMap){

 

List childrenList=null;

Dao dao = new DaoImp();

try{

String nodeType=(String)paramsMap.get("nodeType");

//Constants.orgTotal.equals(branchId);

StringBuffer sb = new StringBuffer();

if("1".equals(nodeType)){

//根节点上单击,查询所有评分卡的得分

 

sb.append("select ec.folder_id , ec.folder_name,ecs.score,")

.append("ecs.maxscore as maxscore, case when ecs.maxscore=0 then 0 else (ecs.score*100/ecs.maxscore) end ||'%'  as scorepct,   rundate  ")

.append(" from appdqc.estimate_card ec ")

.append(" left join estimate_card_score ecs on ec.folder_id = ecs.folder_id")

.append(" where ec.status='1'  ")

.append(" and rundate='").append(paramsMap.get("runDate")).append("'")

.append(" and org_id='").append(paramsMap.get("orgCode")).append("'")

.append(" and ec.par_folder_id='").append(paramsMap.get("nodeId")).append("'");

 

}else if("2".equals(nodeType)){

//某个评分卡上非叶子节点上的单击

sb.append("select ec.folder_id , ec.folder_name,ecs.score,")

.append("ecs.maxscore as maxScore,case when ecs.maxscore=0 then 0 else (ecs.score*100/ecs.maxscore) end||'%'  as scorepct,   rundate  ")

.append(" from appdqc.estimate_group ec ")

.append(" left join estimate_card_score ecs on ec.folder_id = ecs.folder_id")

.append(" where ec.status='1'  ")

.append(" and rundate='").append(paramsMap.get("runDate")).append("'")

.append(" and org_id='").append(paramsMap.get("orgCode")).append("'")

.append(" and ec.par_folder_id='").append(paramsMap.get("nodeId")).append("'");

}else if("3".equals(nodeType)){

//某个评分卡上叶子节点的单击

sb.append("select ecr.check_id as folder_id , r.rule_name as folder_name,rs.score,")

.append("ecr.weight*100 as maxScore,(rs.score/ecr.weight)||'%'  as scorepct,rs.rundate ,ecr.weight")

.append(" ,r.sys_id,r.table_name,r.check_column_name")

.append(" from appdqc.estimate_checkid_relation ecr ")

.append(" left join rule_score rs on ecr.check_id = rs.check_id")

.append(" left join rule r on ecr.check_id= r.check_id")

.append(" where 1=1  ")

.append(" and rs.rundate='").append(paramsMap.get("runDate")).append("'")

.append(" and rs.org_id='").append(paramsMap.get("orgCode")).append("'")

.append(" and ecr.folder_id='").append(paramsMap.get("nodeId")).append("'");

}

 

childrenList=dao.getResultList(sb.toString());

 

}catch(Exception e){

e.printStackTrace();

}

return childrenList;

}

 

public List getLatestCalDate(){

 

Dao dao = new DaoImp();

List childrenList=null;

try{

StringBuffer sb = new StringBuffer();

//某个评分卡上叶子节点的单击

sb.append("select max(rundate) as maxRundate ")

.append(" from estimate_card_score ")

;

 

childrenList=dao.getResultList(sb.toString());

 

}catch(Exception e){

e.printStackTrace();

}

return childrenList;

}

 

 

public List setGroupWeightQuery(Map<String, Object> paramsMap){

 

List groupList=null;

Dao dao = new DaoImp();

 

String groupIds[] = ((String)paramsMap.get("groupIds")).split("#");

try{

StringBuffer sb = new StringBuffer();

sb.append("select folder_id , folder_name,folder_desc,par_folder_id as parId,")

.append("weight,create_oper,start_dt,dis_weight ")

.append("from appdqc.estimate_group ")

.append(" where status='1' and ")

.append(" folder_id in "+CommonFunc.getInStr(groupIds));

 

groupList=dao.getResultList(sb.toString());

 

}catch(Exception e){

e.printStackTrace();

}

return groupList;

}

 

public List setRuleWeightQuery(Map<String, Object> paramsMap){

 

List ruleList=null;

Dao dao = new DaoImp();

 

String ruleIds[] = ((String)paramsMap.get("ruleIds")).split("#");

try{

StringBuffer sb = new StringBuffer();

 

sb.append("select er.check_id , er.check_id,sys_id,table_name,check_column_name,")

.append("er.start_dt,er.create_oper,er.weight,er.dis_weight,rg.rule_name ")

.append("from appdqc.estimate_checkid_relation er ")

.append(" left join appdqc.rule rg on rg.check_id = er.check_id ")

.append(" where rg.status='1' and er.status='1' and ")

.append(" er.check_id in "+CommonFunc.getInStr(ruleIds));

 

ruleList=dao.getResultList(sb.toString());

 

}catch(Exception e){

e.printStackTrace();

}

return ruleList;

}

 

 

public List estimateCard(){

 

Dao dao = new DaoImp();

try{

 

StringBuffer sb = new StringBuffer();

 

sb.append("SELECT FOLDER_ID||'#'||FOLDER_NAME as id , FOLDER_NAME as name from estimate_card where par_folder_id='-1' and status=1 ");

 

String orderBy = " order by FOLDER_ID ";

sb.append(orderBy);

List cardList = dao.getResultList(sb.toString());

 

return cardList;

 

}catch(Exception e){

e.printStackTrace();

}finally{

dao.closeconn();

}

return null;

}

 

 

public int estimateGroupAdd(Map<String, Object> paramsMap){

Dao dao = new DaoImp();

try{

 

SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

String currDate = df.format(new Date());

 

StringBuffer sb = new StringBuffer();

sb.append("insert into estimate_group ")

.append("select coalesce(case  when max(zeroifnull(folder_id)) =0 then 10000 ")

.append("             when max(zeroifnull(folder_id))>=1 then max(zeroifnull(folder_id))+1 end,10000),'")

.append(paramsMap.get("parId")).append("','")

.append(paramsMap.get("FOLDER_NAME")).append("','")

.append(paramsMap.get("FOLDER_DESC")).append("','0','1','','','")

.append(paramsMap.get("userId")).append("','0','")

.append(currDate).append("','")

.append("2999-12-31 00:00:00',''")

.append(" from estimate_group ");

 

int ret = dao.insert(sb.toString());

 

return ret;

 

}catch(Exception e){

e.printStackTrace();

}finally{

dao.closeconn();

}

return 0;

}

 

public int estimateGroupModify(Map<String, Object> paramsMap){

Dao dao = new DaoImp();

try{

StringBuffer updateSQL = new StringBuffer();

updateSQL

.append("update estimate_group set ")

.append(" FOLDER_NAME='").append(paramsMap.get("FOLDER_NAME")).append("',")

.append(" FOLDER_DESC='").append(paramsMap.get("FOLDER_DESC")).append("'")

.append(" where folder_id='").append(paramsMap.get("FOLDER_ID")).append("' and status='1'");

 

dao.update(updateSQL.toString());

     

 

}catch(Exception e){

e.printStackTrace();

return 0;

}finally{

dao.closeconn();

}

return 1;

}

 

public int estimateGroupModify_history(Map<String, Object> paramsMap){

Dao dao = new DaoImp();

try{

SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

String currDate = df.format(new Date());

 

StringBuffer updateSQL = new StringBuffer();

updateSQL

.append("update estimate_group set ")

.append(" END_DT='").append(currDate).append("',")

.append(" status='0' ")

.append(" where folder_id='").append(paramsMap.get("FOLDER_ID")).append("' and status='1'");

 

dao.update(updateSQL.toString());

     

StringBuffer insertSQL = new StringBuffer();

insertSQL

.append(" insert into estimate_group ")

.append(" select distinct  FOLDER_ID,PAR_FOLDER_ID,'")

.append(paramsMap.get("FOLDER_NAME")).append("','")

.append(paramsMap.get("FOLDER_DESC")).append("',")

.append("isleaf,'1',")

.append(" weight,display_order,create_oper,node_level,'")

.append(currDate).append("','2999-12-31 00:00:00'")

.append(" from estimate_group ")

.append(" where folder_id='").append(paramsMap.get("FOLDER_ID")).append("'");

 

dao.insert(insertSQL.toString());

 

}catch(Exception e){

e.printStackTrace();

return 0;

}finally{

dao.closeconn();

}

return 1;

}

public int estimateGroupDelete(String []estimateGroupIDs){

 

Dao dao = new DaoImp();

try{

SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

String currDate = df.format(new Date());

 

StringBuffer sb = new StringBuffer();

 

sb.append(" WITH  recursive temptab(id,name,pid,leveld) as(")

    .append(" select a.folder_id,a.folder_name,a.par_folder_id,0 from estimate_group a where folder_id in"+CommonFunc.getInStr(estimateGroupIDs)+ "union all")

    .append(" select a.folder_id,a.folder_name,a.par_folder_id,c.leveld+1 from estimate_group a ,temptab c where  c.id = a.par_folder_id and status='1' and leveld<7)")

    .append(" select id from temptab");

       

List childrenList = dao.getResultList(sb.toString());

 

String []allIds = new String[childrenList.size()];

for(int i =0;i<childrenList.size();i++){

allIds[i] = (String)((Map)childrenList.get(i)).get("ID");

}

 

StringBuffer updateSQL = new StringBuffer();

updateSQL

.append("update  estimate_group set  status='0', ")

.append(" END_DT='").append(currDate).append("'")

.append(" where folder_id in "+CommonFunc.getInStr(allIds));

int ret = dao.update(updateSQL.toString());

return ret;

 

}catch(Exception e){

e.printStackTrace();

return 0;

}finally{

dao.closeconn();

}

}

 

public int updateWeight(String []estimateGroupIDs){

 

Dao dao = new DaoImp();

try{

SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

String currDate = df.format(new Date());

 

String id = "";

String weight="";

String disWeight="";

for(int i =0;i<estimateGroupIDs.length;i++){

id = estimateGroupIDs[i].split("@")[0];

disWeight = estimateGroupIDs[i].split("@")[4];

weight = estimateGroupIDs[i].split("@")[5];

StringBuffer updateSQL = new StringBuffer();

updateSQL

.append("update  estimate_group set  weight='"+weight+"',dis_weight='"+disWeight+"'")

.append(" where folder_id='"+id+"'");

dao.update(updateSQL.toString());

}

return 1;

 

}catch(Exception e){

e.printStackTrace();

return 0;

}finally{

dao.closeconn();

}

}

 

public int setRuleWeight(Map<String, Object> paramsMap){

 

Dao dao = new DaoImp();

try{

String []ruleInfo = ((String)paramsMap.get("ruleWeightParams")).split(",");

String currNodeId = (String)paramsMap.get("currNodeId");

 

//得到当前设置规则的父节点的权重信息

StringBuffer leafNodeWeightSQL = new StringBuffer();

leafNodeWeightSQL

.append("select folder_id,folder_name,weight")

.append(" from estimate_group ")

.append(" where folder_id = '"+currNodeId+"' and status='1' ");

List leafNodeWeight = dao.getResultList(leafNodeWeightSQL.toString());

String leafWeight = (String)((Map)leafNodeWeight.get(0)).get("WEIGHT");

 

String ruleID = "";

String disWeight="";

BigDecimal disWeightTotal = new BigDecimal(0.0);

List rules = new ArrayList();

for(int i =0;i<ruleInfo.length;i++){

ruleID = ruleInfo[i].split("@")[0];

rules.add(ruleID);

disWeight = ruleInfo[i].split("@")[1];

disWeightTotal = disWeightTotal.add(new BigDecimal(disWeight));

BigDecimal weight = new BigDecimal(disWeight).multiply(new BigDecimal(leafWeight)).setScale(4, BigDecimal.ROUND_HALF_UP);

StringBuffer updateSQL = new StringBuffer();

updateSQL

.append("update  estimate_checkid_relation set  dis_weight='"+disWeight+"',weight='"+weight.toString()+"'")

.append(" where folder_id='"+currNodeId+"' and check_id='"+ruleID+"'");

dao.update(updateSQL.toString());

}

 

//精度4位,四舍五入,剩余的分配权重

BigDecimal remainDisWeight = new BigDecimal(1-disWeightTotal.doubleValue()).setScale(4, BigDecimal.ROUND_HALF_UP);

//未设置规则权重的其他规则平分该权重

BigDecimal remainActWeight = remainDisWeight.multiply(new BigDecimal(leafWeight));

 

System.out.println("**************剩余的分配权重:"+remainDisWeight.doubleValue());

System.out.println("**************剩余的实际权重:"+remainActWeight.doubleValue());

 

StringBuffer queryRemainRule = new StringBuffer();

 

String checkids = CommonFunc.getInStr(rules);

 

System.out.println("**************已经设置过的checkId:"+checkids);

 

queryRemainRule

.append("select count(*) as num from estimate_checkid_relation ")

.append("  where folder_id='"+currNodeId+"' and check_id not in "+checkids);

 

List count = dao.getResultList(queryRemainRule.toString());

String notSetWeight = (String)((Map)count.get(0)).get("NUM");

 

//如果规则的权重已经全部分配,就不需要均等分配

if(!"0".equals(notSetWeight)){

BigDecimal oddDis = remainDisWeight.divide(new BigDecimal(notSetWeight),4,BigDecimal.ROUND_HALF_UP);

BigDecimal oddAct = remainActWeight.divide(new BigDecimal(notSetWeight),4,BigDecimal.ROUND_HALF_UP);

 

StringBuffer updateRemainRule = new StringBuffer();

updateRemainRule

.append("update  estimate_checkid_relation set  dis_weight='"+oddDis.toString()+"',weight='"+oddAct.toString()+"'")

.append(" where folder_id='"+currNodeId+"' and check_id not in "+checkids);

dao.update(updateRemainRule.toString());

}

 

return 1;

 

}catch(Exception e){

e.printStackTrace();

return 0;

}finally{

dao.closeconn();

}

}

 

public int updateRuleWeight(String []ruleIDs){

 

Dao dao = new DaoImp();

try{

String ruleID = "";

String folderID = ruleIDs[0].split("@")[0];

String disWeight="";

String weight = "";

BigDecimal bg = new BigDecimal(0.0);

List rules = new ArrayList();

for(int i =1;i<ruleIDs.length;i++){

ruleID = ruleIDs[i].split("@")[0];

rules.add(ruleID);

disWeight = ruleIDs[i].split("@")[1];

bg = bg.add(new BigDecimal(disWeight));

weight = ruleIDs[i].split("@")[2];

StringBuffer updateSQL = new StringBuffer();

updateSQL

.append("update  estimate_checkid_relation set  dis_weight='"+disWeight+"',weight='"+weight+"'")

.append(" where folder_id='"+folderID+"' and check_id='"+ruleID+"'");

dao.update(updateSQL.toString());

}

 

//剩余的分配权重

double rema = 1-bg.doubleValue();

 

BigDecimal folderActWeight = new BigDecimal(ruleIDs[0].split("@")[1]);

//精度4位,四舍五入,剩余的分配权重

BigDecimal remainDisWeight = new BigDecimal(rema).setScale(4, BigDecimal.ROUND_HALF_UP);

//未设置规则权重的其他规则平分该权重

BigDecimal remainActWeight = remainDisWeight.multiply(folderActWeight);

 

System.out.println("**************叶子节点的权重:"+ruleIDs[0].split("@")[1]);

System.out.println("**************剩余的分配权重:"+remainDisWeight.doubleValue());

System.out.println("**************剩余的实际权重:"+remainActWeight.doubleValue());

//一个叶子评分组下可能有多个规则,实际设置分配权重时只设置几个

 

StringBuffer queryRemainRule = new StringBuffer();

 

String checkids = CommonFunc.getInStr(rules);

 

System.out.println("**************已经设置过的checkId:"+checkids);

 

queryRemainRule

.append("select count(*) as num from estimate_checkid_relation ")

.append("  where folder_id='"+folderID+"' and check_id not in "+checkids);

 

List count = dao.getResultList(queryRemainRule.toString());

String notSetWeight = (String)((Map)count.get(0)).get("NUM");

 

//如果规则的权重已经全部分配,就不需要均等分配

if(!"0".equals(notSetWeight)){

BigDecimal oddDis = remainDisWeight.divide(new BigDecimal(notSetWeight)).setScale(4, BigDecimal.ROUND_HALF_UP);

BigDecimal oddAct = remainActWeight.divide(new BigDecimal(notSetWeight)).setScale(4, BigDecimal.ROUND_HALF_UP);

 

StringBuffer updateRemainRule = new StringBuffer();

updateRemainRule

.append("update  estimate_checkid_relation set  dis_weight='"+oddDis.toString()+"',weight='"+oddAct.toString()+"'")

.append(" where folder_id='"+folderID+"' and check_id not in "+checkids);

dao.update(updateRemainRule.toString());

}

 

return 1;

 

}catch(Exception e){

e.printStackTrace();

return 0;

}finally{

dao.closeconn();

}

}

 

public int updateGroupWeight(Map<String, Object> paramsMap){

 

Dao dao = new DaoImp();

try{

 

String []groupInfo = ((String)paramsMap.get("groupWeightParams")).split(",");

String currNodeId = (String)paramsMap.get("currNodeId");

 

String par_folder_id = currNodeId;

String parWeight = "";

 

if(currNodeId.length()<5){

//当前操作的节点是评分卡,评分卡没有权重和分配权重,要特殊处理

parWeight = "1.0000";

}else{

//得到当前设置节点的父节点的权重信息

StringBuffer parGroupSQL = new StringBuffer();

parGroupSQL

.append("select egp.folder_id,egp.folder_name,egp.weight")

.append(" from estimate_group egs")

.append(" left join estimate_group egp on egs.par_folder_id = egp.folder_id")

.append(" where egs.folder_id = '"+groupInfo[0].split("@")[0]+"' and egs.status='1' ")

.append(" and egp.status='1' ");

List parGroupInfo = dao.getResultList(parGroupSQL.toString());

parWeight = (String)((Map)parGroupInfo.get(0)).get("WEIGHT");

}

String disWeight="";

BigDecimal weight = new BigDecimal(0.0000);

String groupId ="";

//正在设置权重的所有评分组

List groupIds = new ArrayList();

//正在设置权重的所有评分组的分配权重之和

BigDecimal disWeightTotal = new BigDecimal(0.0);

for(int i =0;i<groupInfo.length;i++){

groupId = groupInfo[i].split("@")[0];

groupIds.add(groupId);

disWeight = groupInfo[i].split("@")[1];

disWeightTotal = disWeightTotal.add(new BigDecimal(disWeight));

//计算每一个设置了分配权重的评分组节点的实际权重=父节点实际权重*自身的分配权重

weight = new BigDecimal(disWeight).multiply(new BigDecimal(parWeight)).setScale(4, BigDecimal.ROUND_HALF_UP);

StringBuffer updateSQL = new StringBuffer();

updateSQL

.append("update  estimate_group set  dis_weight='"+disWeight+"',weight='"+weight+"'")

.append(" where folder_id='"+groupId+"'");

dao.update(updateSQL.toString());

oddBelowWeight(groupId,dao);

}

 

System.out.println("**************已经设置过的groupIds:"+groupIds.toString());

 

StringBuffer queryRemainGroup = new StringBuffer();

 

queryRemainGroup

.append("select folder_id from estimate_group ")

.append("  where status='1' and par_folder_id='"+par_folder_id+"' and folder_id not in "+CommonFunc.getInStr(groupIds));

 

List groupList = dao.getResultList(queryRemainGroup.toString());

if(groupList==null){

groupList = new ArrayList();

}

int notSetWeightNum = groupList.size();

//同一个父节点下的评分组没有全部设置,只设置了部分

if(notSetWeightNum>0){

 

//精度4位,四舍五入,剩余的分配权重

BigDecimal remainDisWeight = new BigDecimal(1-disWeightTotal.doubleValue()).setScale(4, BigDecimal.ROUND_HALF_UP);

BigDecimal remainActWeight = remainDisWeight.multiply(new BigDecimal(parWeight));

 

BigDecimal oddDis = remainDisWeight.divide(new BigDecimal(notSetWeightNum),4,BigDecimal.ROUND_HALF_UP);

BigDecimal oddAct = remainActWeight.divide(new BigDecimal(notSetWeightNum),4,BigDecimal.ROUND_HALF_UP);

 

StringBuffer updateRemainGroup = new StringBuffer();

updateRemainGroup

.append("update  estimate_group set  dis_weight='"+oddDis.toString()+"',weight='"+oddAct.toString()+"'")

.append(" where status='1' and par_folder_id='"+par_folder_id+"' and folder_id not in "+ CommonFunc.getInStr(groupIds));

dao.update(updateRemainGroup.toString());

 

String tmpId  ="";

for(int i=0;i<groupList.size();i++){

tmpId = (String)((Map)groupList.get(i)).get("FOLDER_ID");

oddBelowWeight(tmpId,dao);

}

}

 

return 1;

 

}catch(Exception e){

e.printStackTrace();

return 0;

}finally{

dao.closeconn();

}

}

 

//均分下级节点所有的分配权重和实际权重

public int oddBelowWeight(String folder_id,Dao dao ) throws Exception{

 

StringBuffer nodeInfoSQL = new StringBuffer();

nodeInfoSQL

.append("select folder_id,folder_name,isleaf,weight")

.append(" from estimate_group ")

.append(" where folder_id='"+folder_id+"' and status='1' " );

 

List nodeInfo = dao.getResultList(nodeInfoSQL.toString());

String isLeaf = (String)((Map)nodeInfo.get(0)).get("ISLEAF");

//当前节点的实际权重

String weight = (String)((Map)nodeInfo.get(0)).get("WEIGHT");

 

if("0".equals(isLeaf)){

//不是叶子节点,需要进行递归计算

StringBuffer belowNodeInfoSQL = new StringBuffer();

belowNodeInfoSQL

.append("select count(folder_id) as NUM from estimate_group")

.append(" where status='1' and par_folder_id='"+folder_id+"'");

//评分树末端必须都是叶子节点,也即都设置了规则,否则这里的count=0,出现除0异常

List childList = dao.getResultList(belowNodeInfoSQL.toString());

String childCount = (String)((Map)childList.get(0)).get("NUM");

 

if(!"0".equals(childCount)){

BigDecimal oddDis = new BigDecimal(1.0000).divide(new BigDecimal(childCount),4,BigDecimal.ROUND_HALF_UP);

BigDecimal oddAct = new BigDecimal(weight).divide(new BigDecimal(childCount),4,BigDecimal.ROUND_HALF_UP);

 

//均分当前节点的下级节点信息

StringBuffer updateChildGroup = new StringBuffer();

updateChildGroup

.append("update  estimate_group set  dis_weight='"+oddDis.toString()+"',weight='"+oddAct.toString()+"'")

.append(" where status='1' and  par_folder_id='"+folder_id+"'");

dao.update(updateChildGroup.toString());

 

//对所有的下级进行递归的权重计算

StringBuffer belowNodeIDSQL = new StringBuffer();

belowNodeIDSQL

.append("select folder_id from estimate_group")

.append(" where status='1' and par_folder_id='"+folder_id+"'");

List folderIds = dao.getResultList(belowNodeIDSQL.toString());

String tmpIds = "";

for(int i=0;i<folderIds.size();i++){

tmpIds = (String)((Map)folderIds.get(i)).get("FOLDER_ID");

oddBelowWeight(tmpIds,dao);

}

}

 

}else if("1".equals(isLeaf)){

//是叶子节点,只需均分节点下的规则分配权重和实际权重

//对所有的下级进行递归的权重计算

StringBuffer ruleSQL = new StringBuffer();

ruleSQL

.append("select count(check_id)as NUM from estimate_checkId_relation")

.append(" where folder_id='"+folder_id+"' and status='1'");

 

List count = dao.getResultList(ruleSQL.toString());

String ruleNum = (String)((Map)count.get(0)).get("NUM");

 

BigDecimal oddDis = new BigDecimal(1.0000).divide(new BigDecimal(ruleNum),4,BigDecimal.ROUND_HALF_UP);

BigDecimal oddAct = new BigDecimal(weight).divide(new BigDecimal(ruleNum),4,BigDecimal.ROUND_HALF_UP);

 

//均分当前节点的下级节点信息

StringBuffer updateChildRule = new StringBuffer();

updateChildRule

.append("update  estimate_checkId_relation set  dis_weight='"+oddDis.toString()+"',weight='"+oddAct.toString()+"'")

.append(" where folder_id='"+folder_id+"' and status='1'");

dao.update(updateChildRule.toString());

 

}

return 1;

}

 

public List getScoreInfo(Map scoreParam,String currId){

 

List scoreList=null;

Dao dao = new DaoImp();

try{

StringBuffer sb = new StringBuffer();

sb.append("select folder_id, score ")

.append ("from estimate_card_score ")

.append(" where 1=1 ")

.append(" and folder_id='").append(currId).append("'")

.append(" and rundate='").append(scoreParam.get("runDate")).append("'")

.append(" and org_id='").append(scoreParam.get("orgCode")).append("'");

 

scoreList=dao.getResultList(sb.toString());

 

}catch(Exception e){

e.printStackTrace();

}

 

return scoreList;

}

 

public List getMaxScore(Map scoreParam,String currId){

 

List scoreList=null;

Dao dao = new DaoImp();

try{

StringBuffer sb = new StringBuffer();

sb.append(" select folder_id, maxscore ")

.append ("from estimate_card_score ")

.append(" where 1=1 ")

.append(" and folder_id='").append(currId).append("'")

.append(" and rundate='").append(scoreParam.get("runDate")).append("'")

.append(" and org_id='").append(scoreParam.get("orgCode")).append("'");

 

scoreList=dao.getResultList(sb.toString());

 

}catch(Exception e){

e.printStackTrace();

}

 

return scoreList;

}

 

 

public List getChildren(String id){

 

List childrenList=null;

Dao dao = new DaoImp();

try{

StringBuffer sb = new StringBuffer();

sb.append("select folder_id, folder_name,par_folder_id,isleaf,dis_weight,weight ")

.append ("from estimate_group ")

.append(" where status ='1' ")

.append(" and par_folder_id='"+id+"'")

//.append(" and Start_Dt<'").append(currDate).append("'")

//.append(" and end_Dt>'").append(currDate).append("'")

.append(" order by start_dt");

 

childrenList=dao.getResultList(sb.toString());

}catch(Exception e){

e.printStackTrace();

}

 

return childrenList;

}

 

public List searchRuleGroupByGroupId(Map<String, Object> paramsMap){

 

List childrenList=null;

Dao dao = new DaoImp();

 

try{

StringBuffer sb = new StringBuffer();

sb.append("select er.folder_id , folder_name,er.check_id,sys_id,table_name,check_column_name,")

.append("er.start_dt,er.create_oper,rs.score")

.append(" from appdqc.estimate_checkid_relation er ")

.append(" left join appdqc.estimate_group  eg on er.folder_id = eg.folder_id")

.append(" left join appdqc.rule rg on rg.check_id = er.check_id ")

.append(" left join appdqc.rule_score  rs on er.check_id = rs.check_id")

.append(" where er.status='1' and er.folder_id='").append(paramsMap.get("nodeId")).append("'")

.append(" and rs.rundate= date '").append(paramsMap.get("check_date")).append("'")

.append(" and org_id='").append(paramsMap.get("branchId")).append("'")

/*.append(" and er.start_dt<'").append(currDate)

.append("' and er.end_dt >'").append(currDate).append("'")

.append(" and eg.start_dt<'").append(currDate)

.append("' and eg.end_dt >'").append(currDate).append("'")*/;

 

childrenList=dao.getResultList(sb.toString());

 

}catch(Exception e){

e.printStackTrace();

}

return childrenList;

}

 

public List searchBelowOrgRuleScore(Map<String, Object> paramsMap){

 

List ruleScoreList=null;

Dao dao = new DaoImp();

String inOrgs = derectBelowOrg((String)paramsMap.get("branchId"), dao);

 

try{

StringBuffer sb = new StringBuffer();

sb.append("select r.check_id , r.rule_name,r.sys_id,r.table_name,r.check_column_name,")

.append("rs.score,rs.rundate as rundate,branch_id,branch_name,ecr.weight,cr.pass_rate*100||'%' ")

.append(" from appdqc.rule_score rs ")

.append(" left join estimate_checkid_relation ecr on rs.check_id = ecr.check_id and ecr.status='1'")

.append(" left join check_result cr on rs.check_id = cr.check_id and rs.org_id = cr.org_id ")

.append(" left join appdqc.rule  r on rs.check_id = r.check_id")

.append(" left join bohz_appmds.tap_c_branch_all tb on rs.org_id = tb.branch_id ")

.append(" where rs.check_id='").append(paramsMap.get("nodeId")).append("'")

.append(" and cr.rundate= date '").append(paramsMap.get("check_date")).append("'")

.append(" and rs.rundate= date '").append(paramsMap.get("check_date")).append("'")

.append(" and cr.org_id in "+inOrgs)

.append(" and rs.org_id in "+inOrgs)

.append(" order by rs.org_id");

;

 

ruleScoreList=dao.getResultList(sb.toString());

 

}catch(Exception e){

e.printStackTrace();

}

return ruleScoreList;

}

public List searchBelowOrgGroupScore(Map<String, Object> paramsMap){

 

List childrenList=null;

Dao dao = new DaoImp();

 

String inOrgs = derectBelowOrg((String)paramsMap.get("branchId"), dao);

try{

StringBuffer sb = new StringBuffer();

sb.append("select org_id,branch_name as orgname,rundate,score,er.maxScore,")

.append("case when er.maxscore=0 then 0 else ( er.score*100/er.maxScore) end ||'%'  as scorepct")

.append(" from appdqc.estimate_card_score er ")

.append(" left join bohz_appmds.tap_c_branch_all cb on er.org_id = cb.branch_id")

.append(" left join estimate_group eg on er.folder_id = eg.folder_id and eg.status='1' ")

.append(" where er.folder_id='").append(paramsMap.get("nodeId")).append("'")

.append(" and rundate= date '").append(paramsMap.get("check_date")).append("'")

.append(" and org_id in"+inOrgs);

 

childrenList=dao.getResultList(sb.toString());

 

}catch(Exception e){

e.printStackTrace();

}

return childrenList;

}

 

//当前机构的直接下级

public String derectBelowOrg(String currOrg,Dao dao){

 

StringBuffer sb = new StringBuffer();

sb.append(" select a.branch_id as id " +

" from  bohz_appmds.tap_c_branch_all " +

" a where par_branch_id = '"+currOrg+"'");

List childOrgList = null;

try {

childOrgList = dao.getResultList(sb.toString());

} catch (Exception e) {

e.printStackTrace();

return "('')";

}

String orgInStr = CommonFunc.getInStr(childOrgList, "ID");

return orgInStr;

}

 

public List searchRuleWeightByGroupId(Map<String, Object> paramsMap){

 

List childrenList=null;

Dao dao = new DaoImp();

SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

String currDate = df.format(new Date());

 

try{

StringBuffer sb = new StringBuffer();

sb.append("select er.folder_id , folder_name,er.check_id,sys_id,table_name,check_column_name, ")

.append("er.start_dt,er.create_oper,er.weight,er.dis_weight,rg.rule_name ")

.append(" from appdqc.estimate_checkid_relation er ")

.append(" left join appdqc.estimate_group  eg on er.folder_id = eg.folder_id and eg.status='1' ")

.append(" left join appdqc.rule rg on rg.check_id = er.check_id ")

.append(" where er.status='1' and er.folder_id='").append(paramsMap.get("nodeId")).append("'");

 

childrenList=dao.getResultList(sb.toString());

 

}catch(Exception e){

e.printStackTrace();

}

return childrenList;

}

 

 

public int calCardScore(Map<String, Object> paramsMap){

 

Dao dao = new DaoImp();

String currDate = "2015-12-12 19:12:10";

try{

//规则组的设置内容太多,和显示树的主表进行了数据分离,处理时要单独进行

//1 先通过规则组的得分和权重,计算叶子评分组的得分

String sql = "(select group_Id from estimate_mapid_relation " +

"where "+currDate+" between start_dt and end_dt group by group_Id)tmp" +

"inner join map_score ms on tmp.map_id = ms.map_id";

List l = dao.getResultList(sql);

for(int i = 0;i<l.size();i++){//对每一个设置了规则组的叶子评分组进行加权计算,这里的评分组可能输来自于不同的评分卡

String groupId = (String)((Map)l.get(i)).get("GROUP_ID");

String groupId_rulegroup_sql = "select map_id,ESTIMATE_WEIGHT,ms.score from estimate_mapid_relation er" +

" inner join map_score ms on er.map_id = ms.map_id  " +

"where group_Id='"+groupId+"' " +

" and er.start_dt<date and er.ent_dt>date";

List ll = dao.getResultList(groupId_rulegroup_sql);

double gs = 0;

for(int ii = 0;ii<ll.size();ii++){//一个叶子评分组下设置的规则组

double s = Double.valueOf((String)((Map)ll.get(ii)).get("SCORE"));

double w = Double.valueOf((String)((Map)ll.get(ii)).get("ESTIMATE_WEIGHT"));

gs += s*w;

}

String insert = "insert into estimate_card_score " +

"select org,date,folder_id,par_folder_id,folder_name,"+gs +

"from estimate_card " +

" where folder_id='"+groupId+"'";

dao.insert(insert);

}

 

//2 在主表上,通过第一步得到的叶子评分组的score,计算树上每一个节点的score

String pfkSQL = "select folder_id from estimate_card where par_folder_id=1 and status=1 and folder_id=5";//得到所有的评分卡

List ll = dao.getResultList(pfkSQL);

for(int ii = 0;ii<ll.size();ii++){

String curr_folder_id = (String)((Map)ll.get(ii)).get("FOLDER_ID");

calNodeScore(curr_folder_id);

}

 

 

}catch(Exception e){

e.printStackTrace();

}finally{

dao.closeconn();

}

return 0;

}

 

/**

* 递归计算当前节点的得分=子节点的加权得分

* 前提是,当前节点不能即添加了评分组,又添加了规则组

* @param curr_folder_id

* @return

*/

public double calNodeScore(String curr_folder_id){

 

Dao dao = new DaoImp();

double gs = 0;//当前节点的加权结果,若当前节点包括叶子和非叶子节点

try{

String pfkSQL = "select folder_id,isleaf,weight,score from estimate_card where status=1 and par_folder_id="+curr_folder_id;

List ll = dao.getResultList(pfkSQL);

 

for(int ii = 0;ii<ll.size();ii++){//当前节点的子节点,包括可能的叶子节点和非叶子节点

String child_folder_id = (String)((Map)ll.get(ii)).get("FOLDER_ID");

String isleaf = (String)((Map)ll.get(ii)).get("ISLEAF");

String weight = (String)((Map)ll.get(ii)).get("WEIGHT");

if("0".equals(isleaf)){

//当前节点的非叶子子节点的计算结果

double ret = calNodeScore(child_folder_id);

//还要和兄弟节点的结果进行加权

gs += (ret*Double.valueOf(weight));

}else{

//当前节点的叶子子节点,得分从计算结果表里面获得

String leafScoreSQL = "select * from estimate_card_score ec inner join estimate_card on folder_id="+child_folder_id

+" and ec.org='340010001' and date='20150520'";

Map score = (Map)dao.getResultList(leafScoreSQL).get(0);//结果集理论只有一条

 

double s = Double.valueOf((String)(score.get("SCORE")));

double w = Double.valueOf((String)(score.get("WEIGHT")));

gs += s*w;

}

}

//插入当前节点的加权得分

String insert = "insert into estimate_card_score " +

"select org,date,folder_id,par_folder_id,folder_name,"+gs +

"from estimate_card " +

" where folder_id='"+curr_folder_id+"'";

dao.insert(insert);

 

}catch(Exception e){

e.printStackTrace();

}finally{

dao.closeconn();

}

return gs;

}

 

/**

* 递归计算当前节点的得分=子节点的加权得分

* 前提是,当前节点不能即添加了评分组,又添加了规则组

* @param curr_folder_id

* @return

*/

public double calNodeScore_bak(String curr_folder_id){

 

Dao dao = new DaoImp();

double gs = 0;//当前节点的加权结果,若当前节点包括叶子和非叶子节点

try{

String pfkSQL = "select folder_id,isleaf,weight,score from estimate_card where status=1 and par_folder_id="+curr_folder_id;

List ll = dao.getResultList(pfkSQL);

 

for(int ii = 0;ii<ll.size();ii++){//当前节点的子节点,包括可能的叶子节点和非叶子节点

String child_folder_id = (String)((Map)ll.get(ii)).get("FOLDER_ID");

String isleaf = (String)((Map)ll.get(ii)).get("ISLEAF");

String weight = (String)((Map)ll.get(ii)).get("WEIGHT");

if("0".equals(isleaf)){

//当前节点的非叶子子节点的计算结果

double ret = calNodeScore(child_folder_id);

//还要和兄弟节点的结果进行加权

gs += (ret*Double.valueOf(weight));

}else{

//当前节点的叶子子节点

double s = Double.valueOf((String)((Map)ll.get(ii)).get("SCORE"));

double w = Double.valueOf((String)((Map)ll.get(ii)).get("WEIGHT"));

gs += s*w;

}

}

//更新当前节点的加权得分

String update = "update estimate_card set score="+gs+" where folder_id="+curr_folder_id;

dao.update(update);

 

}catch(Exception e){

e.printStackTrace();

}finally{

dao.closeconn();

}

return gs;

}

 

//计算规则组的得分

public void calMapScore(String date,String org){

//map_score

Dao dao = new DaoImp();

try{

//评分卡的得分情况细化到农商行,83家

//得到当前机构的所有下级机构

StringBuffer sb = new StringBuffer();

sb.append(" WITH  recursive temporg(id,name,pid,leveld) as(")

    .append(" select a.branch_id,a.branch_name,a.par_branch_id,0 from  bohz_appmds.tap_c_branch_all  a where branch_id ='"+org+ "' union all")

    .append(" 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)")

    .append(" select id from temporg");

List childOrgList = dao.getResultList(sb.toString());

String orgInStr = CommonFunc.getInStr(childOrgList, "ID");

 

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

String resultSQL = " select map_id ,sum(rett) as map_score from" +

"(select main.map_id,main.check_id, " +

"COALESCE(r.rule_weight,COALESCE(r.checkdimension,'100','0.2','200','0.2')) as weight,score,score*weight as rett " +

"from  " +

"(select map_id,check_id,sum(passcount)/sum(effect_count) as score " +

"from check_result " +

"where rundate='20151219' and org_id in"+orgInStr+" group by map_id,check_id) main" +

"inner join rule r on main.check_id = r.check_id" +

") tt group by map_id" ;

 

List resultList = dao.getResultList(resultSQL);

 

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

for(int i=0;i<resultList.size();i++){//进入map_id

String currMapId = (String)((Map)resultList.get(i)).get("map_id");

String score = (String)((Map)resultList.get(i)).get("map_score");

String insertSQL = "insert into map_score values('20151219','"+currMapId+"','"+org+"',"+score+"')";

dao.insert(insertSQL);

}

 

}catch(Exception e){

e.printStackTrace();

}

}

 

}

 

分享到:
评论

相关推荐

    编程五大常用算法,算法数据结构

    在提供的代码示例中,`recursion`函数展示了简单的递归实现,它将输入除以3直到达到基本情况(i == 0)。 2. 动态规划: 动态规划是一种通过组合子问题的解来求解原问题的方法,区别于分治法在于子问题之间可能...

    MonkeyPeach_java_

    首先,我们需要理解问题的基本设定:假设有一组桃子,猴子们希望通过一系列操作将桃子均分。如果桃子数量可以被5整除,那么每个猴子直接分得相同数量即可。然而,当桃子数量不能被5整除时,我们需要找到一种方法,...

    安工大C语言实验2

    实验要求学生输入每个学生每门课的成绩,并计算最高分、最低分和平均分,这需要用到数组遍历、比较操作以及平均值的计算。 总的来说,安工大的C语言实验2旨在通过一系列实践性任务,使学生掌握C语言的核心概念和...

    N枚硬币中找出假币

    代码实现时,可能会用到递归或迭代的方式来模拟这个过程。在编写代码时,需要考虑边界情况,比如当硬币数量为1或2时的特殊情况。此外,由于存在不确定性(假币可能较轻也可能较重),在处理称量结果时需要区分不同的...

    geohash算法实现Java代码

    GeoHash算法会将这个范围不断均分,每次都将当前区域一分为二,分别对应二进制的0和1,如此递归下去,直到达到所需的精度为止。 在Java中实现GeoHash,主要包含以下几个步骤: 1. **坐标标准化**:首先,需要将...

    函数(C语言实验报告).pdf

    `input`函数负责获取学生的学号、姓名和各科成绩,`getsumaver`函数则计算总分和平均分。这两个函数展示了如何使用函数来组织代码,提高代码的可读性和可维护性。 此外,程序中还使用了`if`语句进行条件判断,`...

    C++学生成绩管理系统

    2. 统计班级学生的总分和平均分,并依据平均成绩进行排序。 3. 查询特定科目的不及格学生名单。 4. 输出班级的成绩单。 此外,课程设计还涉及到了分治算法的应用,用于解决平面最接近点对问题,这要求学生掌握分治...

    Java源码猴子分桃问题.rar

    如果余数不为零,那么需要决定如何处理这些无法均分的桃子,可能的选择是丢弃或者再次进行分配。 此外,问题的解决方案可能还涉及了递归思想。递归是一种函数调用自身的技术,可以用来简化复杂问题的求解。猴子分桃...

    4.9测评.pdf

    - **实现思路**:定义一个二维数组存储成绩数据,分别计算每位学生的总分和平均分,以及每门课程的总分和平均分。 2. **水仙花数** - **知识点**:三位数及其各位数字的立方和。 - **实现思路**:遍历 100 至 ...

    MergeSort_Demo.rar_DEMO_Mergeso

    这是因为每次分割都是均分的,因此每次分割后都有log n层,而每层都需要O(n)的时间来合并。 2. 空间复杂度:归并排序需要额外的空间来存储子数组,因此空间复杂度为O(n)。虽然这比原地排序算法如快速排序的平均空间...

    多媒体总结(1)1

    2. 香农-范诺编码是一种熵编码方法,通过概率排序和递归均分字符集实现数据压缩,有效地利用了概率分布。 以上是多媒体技术的基本概念和关键知识点,包括多媒体的定义、媒体元素、MPC、音频处理技术以及数据压缩...

    假币问题的MATLAB求解

    而Coin.m则可能包含了具体的算法实现,比如递归或迭代的方式来逐步缩小假币的可能范围。 具体算法的实现可能会包括以下几个步骤: 1. **初始化**:设定硬币的数量n,创建一个表示所有硬币的数组,其中0代表真币,1...

    Java50道经典题目

    ### Java50道经典题目知识点解析 #### 1. 斐波那契数列 - **知识点**: 斐波那契数列是一个经典的...- **知识点**: 成绩统计和平均分计算。 - **实现方法**: - 读取文件中的学生成绩,计算每门课的平均分并输出结果。

    编写一个程序,求字符串的长度

    数组处理:求最高分、最低分和平均分 C语言中,数组常用于存储一系列数据,如学生分数。通过循环遍历数组,可以轻松地找到最大值、最小值和计算平均值。代码中存在一些细节问题,如`main()`函数声明、初始化变量和...

    二级C语言99套上机操作题

    递归实现的关键在于定义递归终止条件以及递归调用的逻辑。 **代码实现** ```c long fun(int n) { if(n &gt; 1) // 终止条件 return(n * fun(n - 1)); // 递归调用 return 1; // 基础情况 } ``` **填空答案** - 第1...

    day15 python作业

    在给定的Python作业中,有两个主要的知识点:递归函数和学生成绩管理系统的实现。 首先,我们来看第一个问题,这是一个使用递归函数解决的问题。递归是一种编程方法,其中函数通过调用自身来解决问题。在给定的代码...

    数据挖掘18大算法实现以及其他相关经典DM算法

    PageRank算法最早产生于Google,核心思想是通过网页的入链数作为一个网页好快的判定标准,如果1个网页内部包含了多个指向外部的链接,则PR值将会被均分,PageRank算法也会遭到LinkSpan攻击。详细介绍链接 HITS HITS...

    算法分析与设计题目

    可以采用递归的方法来实现,每次递归尝试将剩余的部分继续划分。 #### 4. 序列合并问题 **题目描述**:给定`k`个排好序的序列,使用2路合并算法将这`k`个序列合并成一个序列,并设计一个算法确定合并这个序列的...

Global site tag (gtag.js) - Google Analytics