CREATE OR REPLACE PROCEDURE SP_GET_RANK( V_ORGAN_ID VARCHAR2,--机构编号 V_DATE VARCHAR2, --统计时间 V_PAGEINDEX IN NUMBER, V_PAGESIZE IN NUMBER, V_PAGECOUNT OUT NUMBER, RESULTCURSOR OUT SYS_REFCURSOR ) AS V_ORGANSTR VARCHAR(30000); V_PAGEBEGIN NUMBER(10); V_PAGEEND NUMBER(10); BEGIN V_PAGEBEGIN:=(V_PAGEINDEX-1)*V_PAGESIZE+1; V_PAGEEND:=V_PAGEINDEX*V_PAGESIZE; V_ORGANSTR:=' DECLARE V_ORGAN_ID VARCHAR2(50); V_DATE VARCHAR2(20); V_PAGEBEGIN NUMBER(10); V_PAGEEND NUMBER(10); V_PAGECOUNT NUMBER(10); RESULTCURSOR SYS_REFCURSOR; BEGIN V_ORGAN_ID:=:V_ORGAN_ID; V_DATE:=:V_DATE; V_PAGEBEGIN:=:V_PAGEBEGIN; V_PAGEEND:=:V_PAGEEND; RESULTCURSOR:=:RESULTCURSOR; OPEN RESULTCURSOR FOR SELECT * FROM( SELECT ORGAN_ID, NAME, BASICSCORE, ADD_SCORE, SUBTRACE_SCORE, SCORE, JC_RANK, ROW_NUMBER() OVER(ORDER BY SCORE DESC)NUM FROM( SELECT CODE AS ORGAN_ID, NAME, NVL(BASICSCORE,0)BASICSCORE, NVL(ADD_SCORE,0)ADD_SCORE, NVL(SUBTRACE_SCORE,0)SUBTRACE_SCORE, (NVL(SCORE,0)+NVL(BASICSCORE,0)+40)SCORE, RANK() OVER(ORDER BY (NVL(SCORE,0)+NVL(BASICSCORE,0)+40) DESC)JC_RANK FROM APPRISE_SYS_ORGAN Z1 LEFT JOIN( SELECT ORGAN_ID, SUM(CASE WHEN ADDORSUBFLAG=1 THEN GET_SCORE ELSE 0 END)ADD_SCORE, SUM(CASE WHEN ADDORSUBFLAG=0 THEN GET_SCORE ELSE 0 END)SUBTRACE_SCORE, SUM(GET_SCORE)SCORE FROM( SELECT ORGAN_ID, DETAIL_XM_ID, ADDORSUBFLAG, --如果分值超过每项最分数则取项目的最高分数 (CASE WHEN ABS(SUM(GET_SCORE))>ABS(MAX(DETIALSCORE)) THEN MAX(DETIALSCORE)*(CASE WHEN ADDORSUBFLAG=0 THEN -1 ELSE 1 END) ELSE SUM(GET_SCORE) END) AS GET_SCORE FROM( --人工自动评分 SELECT ID,ORGAN_ID,DETAIL_XM_ID,GET_SCORE,VALID_ENDTIME FROM APPRISE_GRADE_MANAGE_LOG WHERE STATISTICALDATE=V_DATE UNION ALL --工地数量特殊处理 SELECT ID,ORGAN_ID,DETAIL_XM_ID,GET_SCORE,VALID_ENDTIME FROM APPRISE_PROJECT_MANAGE WHERE DETAIL_XM_ID=''0ca08bb5-1891-4653-8d9a-c68c43668214'' AND STATISTICALDATE=V_DATE UNION ALL --监测系统得分 SELECT ID,ORGAN_ID,DETAIL_XM_ID,GET_SCORE,VALID_ENDTIME FROM APPRISE_PROJECT_MANAGE WHERE DETAIL_XM_ID!=''0ca08bb5-1891-4653-8d9a-c68c43668214'' )T1 INNER JOIN APPRAISESTANDARD_CONTENT T2 ON T1.DETAIL_XM_ID=T2.ID WHERE VALID_ENDTIME>TO_DATE(V_DATE,''YYYY-MM-DD'')+1 GROUP BY ORGAN_ID,DETAIL_XM_ID,ADDORSUBFLAG )T2 GROUP BY ORGAN_ID )Z2 ON Z1.CODE=Z2.ORGAN_ID WHERE Z1.TYPE=10 )Z3 WHERE 1=1'|| CASE WHEN V_ORGAN_ID IS NOT NULL THEN ' AND ORGAN_ID=V_ORGAN_ID' ELSE '' END ||')Z4 WHERE NUM BETWEEN V_PAGEBEGIN AND V_PAGEEND; SELECT COUNT(*) INTO V_PAGECOUNT FROM APPRISE_SYS_ORGAN WHERE TYPE=10'|| CASE WHEN V_ORGAN_ID IS NOT NULL THEN ' AND CODE=V_ORGAN_ID ' ELSE '' END ||'; :V_PAGECOUNT:=V_PAGECOUNT; END;'; EXECUTE IMMEDIATE V_ORGANSTR USING V_ORGAN_ID,V_DATE,V_PAGEBEGIN,V_PAGEEND,RESULTCURSOR,OUT V_PAGECOUNT; END;
-- Create table create table APPRISE_PROJECT_MANAGE ( ID VARCHAR2(36) not null, ORGAN_ID VARCHAR2(36), DETAIL_XM_ID VARCHAR2(36), GET_SCORE NUMBER(30,8), OPERATEPEOPLE VARCHAR2(36), GRADESTATE INTEGER, GRADECONTEXT VARCHAR2(500), GRADEDATE DATE, ADDSUBFLAG INTEGER, VALID_STARTTIME DATE, VALID_ENDTIME DATE, STATISTICALDATE DATE, ITEMTYPE INTEGER ) tablespace JB_DATA pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); -- Add comments to the columns comment on column APPRISE_PROJECT_MANAGE.ID is '主键'; comment on column APPRISE_PROJECT_MANAGE.ORGAN_ID is '监测单位'; comment on column APPRISE_PROJECT_MANAGE.DETAIL_XM_ID is '评分项目'; comment on column APPRISE_PROJECT_MANAGE.GET_SCORE is '所得分数'; comment on column APPRISE_PROJECT_MANAGE.OPERATEPEOPLE is '评分人'; comment on column APPRISE_PROJECT_MANAGE.GRADESTATE is '评分状态'; comment on column APPRISE_PROJECT_MANAGE.GRADECONTEXT is '评分内容'; comment on column APPRISE_PROJECT_MANAGE.GRADEDATE is '评分时间'; comment on column APPRISE_PROJECT_MANAGE.ADDSUBFLAG is '加分/减分标志(1加分，0减分)'; comment on column APPRISE_PROJECT_MANAGE.VALID_STARTTIME is '有效开始时间'; comment on column APPRISE_PROJECT_MANAGE.VALID_ENDTIME is '有效结束时间'; comment on column APPRISE_PROJECT_MANAGE.STATISTICALDATE is '纳入平台统计时间'; comment on column APPRISE_PROJECT_MANAGE.ITEMTYPE is '1方案 2 未采集 3异常 4误报 5未及时 6上传频率'; -- Create/Recreate primary, unique and foreign key constraints alter table APPRISE_PROJECT_MANAGE add constraint FK99 primary key (ID) using index tablespace JB_DATA pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );
#region 得到具体项目分数值 public DataTable GetRankScoreDetailDataTable(string v_organ_id, string StateDate, int? pageIndex, int? pageSize, ref int? recordCount, string ADDSUBFLAG) { List<OracleParameter> paras = new List<OracleParameter>(); paras.Add(new OracleParameter("V_ORGAN_ID", v_organ_id)); paras.Add(new OracleParameter("V_DATE", StateDate)); paras.Add(new OracleParameter("V_ADDSUBFLAG", Convert.ToInt32(ADDSUBFLAG))); paras.Add(new OracleParameter("V_PAGEINDEX", pageIndex)); paras.Add(new OracleParameter("V_PAGESIZE", pageSize)); paras.Add(new OracleParameter("V_PAGECOUNT", "0")); paras[5].DbType = DbType.Int32; paras[5].Size = 10; paras[5].Direction = ParameterDirection.Output; DataTable dt = GetEvaluteData("SP_GET_RANK_DETAIL", paras); recordCount = paras[5].Value == null ? 0 : Convert.ToInt32(paras[5].Value); return dt; } #endregion #region 私有方法 /// <summary> /// 调用oracle包，返回DataTable /// </summary> /// <param name="cmdName"></param> /// <param name="paras"></param> /// <returns></returns> private DataTable GetEvaluteData(string cmdName, List<OracleParameter> paras) { ArgumentValidation.CheckForInvalidNullNameReference(cmdName, "cmdName"); DataTable result = null; try { DbCommand dbcmd = dal.DB.GetStoredProcCommand(cmdName); OracleParameter pCursor = new OracleParameter("resultcursor", OracleDbType.RefCursor); pCursor.Direction = ParameterDirection.Output; if (dbcmd != null) { if (paras != null) { foreach (OracleParameter p in paras) { dbcmd.Parameters.Add(p); } } dbcmd.Parameters.Add(pCursor); result = dal.DB.ExecuteDataSet(dbcmd).Tables[0]; } } catch (OracleException ex) { throw new HHHException("数据库异常", ex); } catch (Exception ex) { throw ex; } return result; } #endregion
