CREATEDEFINER=`root`@`localhost`FUNCTION`getAuditDeptFillStatus`(`taskId`varchar(2000),`deptId`varchar(2000),`aduitDeptId`varchar(2000))RETURNSintREADSSQLDATABEGINDECLARE quotaNum INTEGER;# 指标数量DECLARE fillQuotaNum INTEGER;# 指标数量selectcount(*)into quotaNum from department_performance_assessment_quota_item_info where id in(select quota_id from department_performance_assessment_task_quota where task_id = taskId)AND quota_type ='1';selectcount(*)into fillQuotaNum from department_performance_assessment_task_expert_fiill_result where task_id = taskId and dept_id = deptId and fill_type ='2'and fill_source = aduitDeptId and fill_status ='1'and quota_id in(select quota_id from department_performance_assessment_task_quota where task_id = taskId);IF quotaNum <= fillQuotaNum THENRETURN1;ELSERETURN0;ENDIF;END
CREATEDEFINER=`root`@`localhost`FUNCTION`getParentNodeNameWithType`(`nodeId`varchar(2000),`nodeLevel`varchar(2000))RETURNSvarchar(2000)CHARSET utf8
READSSQLDATABEGINDECLARE tempParent VARCHAR(2000);# 临时存放父节点DECLARE tempQuotaNmae VARCHAR(2000);# 临时存放父节点DECLARE tempParentType VARCHAR(2000);# 临时存放父节点类型DECLARE lastParent VARCHAR(2000);# 临时存放父节点SET lastParent ='';SET tempParentType ='';SET tempParent = nodeId;WHILE(tempParent isnotnulland tempParent !=''and lastParent != tempParent and tempParentType != nodeLevel)DO# 循环,用于查询节点上所有的父节点SET lastParent = tempParent;SELECT parent_id,quota_level,quota_name INTO tempParent,tempParentType,tempQuotaNmae FROM department_performance_assessment_quota_item_info where id = lastParent;# 查询节点上所有父节点ENDWHILE;IF(tempParentType = nodeLevel)THENRETURN tempQuotaNmae;# 将结果返回ELSERETURN'';ENDIF;END
CREATEDEFINER=`root`@`localhost`FUNCTION`getQuotaDeptScoreInTask`(`deptId`varchar(2000),`quotaId`varchar(2000),`taskId`varchar(2000),`type`varchar(20))RETURNSdecimal(12,4)READSSQLDATABEGINDECLARE finishScore NUMERIC(12,4);# 最终得分DECLARE sumScore1 NUMERIC(10,2);#定量得分DECLARE sumGoalScore1 NUMERIC(10,2);#定量得分标准DECLARE num1 NUMERIC(10,2);#定量指标数DECLARE sumScore2 NUMERIC(10,2);#定性得分DECLARE sumGoalScore2 NUMERIC(10,2);#定性得分标准DECLARE num2 NUMERIC(10,2);#定性指标数IF(type='1')THEN#按分数计算分数selectcount(*),0.4*a.leader_assessment_score+0.5*a.customer_assessment_score+0.1*(selectAVG(b.total_assessment_score)from department_performance_assessment_task_expert_fiill_result b where b.task_id = a.task_id and b.dept_id = a.dept_id and b.fill_type ='1'and b.quota_id =a.quota_id)into num2,sumScore2 from department_performance_assessment_task_fiill_result a where a.fill_status ='2'and a.task_id = taskId and a.dept_id = deptId and a.quota_id in(select c.id from department_performance_assessment_quota_item_info c where c.quota_type ='1')and FIND_IN_SET(quotaId,getParentNodeList(a.quota_id));selectcount(*),SUM(a.goal_score),SUM(a.finish_score)into num1,sumGoalScore1,sumScore1 from department_performance_assessment_task_fiill_result a where a.fill_status ='2'and a.task_id = taskId and a.dept_id = deptId and a.quota_id in(select id from department_performance_assessment_quota_item_info where quota_type ='2')and FIND_IN_SET(quotaId,getParentNodeList(a.quota_id));IF(num1 >0&& num2 =0)THENreturn sumScore1;ELSEIF(num1 =0&& num2 >0)THENreturn sumScore2;ELSEreturn0.7*sumScore1 +0.3*sumScore2;ENDIF;ENDIF;ELSE#按百分比计算分数selectsum(finish_score)/sum(goal_score)into finishScore from department_performance_assessment_task_fiill_result where fill_status ='2'and task_id = taskId and dept_id = deptId and quota_id in(select id from department_performance_assessment_quota_item_info where quota_type ='2')and FIND_IN_SET(quotaId,getParentNodeList(quota_id));return finishScore;ENDIF;END
CREATEDEFINER=`root`@`localhost`FUNCTION`getRootNode`(`nodeId`varchar(2000))RETURNSvarchar(2000)CHARSET utf8
READSSQLDATABEGINDECLARE parentNode VARCHAR(2000);# 返回父节点结果集DECLARE tempParent VARCHAR(2000);# 临时存放父节点SET parentNode ='';SET tempParent = nodeId;WHILE(tempParent isnotnulland tempParent !=''and parentNode != tempParent)DO# 循环,用于查询节点上所有的父节点SET parentNode = tempParent;# 存入到返回结果中SELECT parent_id INTO tempParent FROM department_performance_assessment_quota_item_info where id = tempParent;# 查询节点的父节点ENDWHILE;RETURN parentNode;# 将返回结果处理,截取掉结果集前面的逗号END
CREATEDEFINER=`root`@`localhost`FUNCTION`getStatus`(`p_year`VARCHAR(10),`p_majorId`VARCHAR(100),`p_tableName`VARCHAR(100))RETURNSvarchar(4)CHARSET utf8mb4
READSSQLDATABEGINDECLARE rtn VARCHAR(4);SELECT approval_status INTO rtn FROM ct_major_report_reason a WHERE a.major_id = p_majorId AND a.YEAR= p_year AND a.table_name =p_tableName;IF( rtn ='0')THENSET rtn ='1';ELSEIF( rtn ='1')THENSET rtn ='2';ELSEIF( rtn ='99'|| rtn ISNULL)THENSET rtn ='cant use sql';ELSESET rtn ='err';ENDIF;ENDIF;ENDIF;RETURN rtn;END