/// <summary> /// 分页取指标结果集 /// </summary> /// <param name="guideLineId"></param> /// <param name="param"></param> /// <param name="filterWord"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="sortBy"></param> /// <param name="sortDirection"></param> /// <param name="requestUser"></param> /// <param name="recordCount"></param> /// <returns></returns> public static DataTable QueryGuideline(string guideLineId, Dictionary <string, object> param, string filterWord, decimal pageIndex, decimal pageSize, string sortBy, string sortDirection, SinoRequestUser requestUser, ref int recordCount) { int getQueryStartTime = Environment.TickCount; int count = 0; DataTable tb = new DataTable("ResultTable"); MD_GuideLine define = GetGuidelineDefine(guideLineId); if (define != null) { string queryStr = GetGuidelineMethod(guideLineId); List <MDQuery_GuideLineParameter> glPara = new List <MDQuery_GuideLineParameter>(); if (param != null && define.Parameters != null) { foreach (var p in param) { MD_GuideLineParameter md_pa = define.Parameters.Find(pa => pa.ParameterName == p.Key); if (md_pa != null) { glPara.Add(new MDQuery_GuideLineParameter(md_pa, p.Value)); } } } foreach (MDQuery_GuideLineParameter gp in glPara) { queryStr = OraQueryBuilder.RebuildGuideLineQueryString(queryStr, gp); } if (requestUser != null) { queryStr = OraQueryBuilder.ReplaceExtSecret(null, queryStr, requestUser); } if (!string.IsNullOrEmpty(filterWord)) { queryStr = string.Format("select * from (\n {0} \n) where {1}", queryStr, filterWord); } try { recordCount = Convert.ToInt32(MysqlDBHelper.ExecuteScalar(MysqlDBHelper.conf, CommandType.Text, string.Format("select count(*) from (\n {0} \n) ", queryStr))); } catch (Exception e) { //OracleLogWriter.WriteSystemLog("Exception :QueryGuideline310行异常,异常信息为" + e.Message, "ERROR"); } queryStr = OraQueryBuilder.BuildPagingSQL(queryStr, pageIndex, pageSize, sortBy, sortDirection); tb = OraQueryModelHelper.FillResultData(queryStr, "ResultTable", ref count); if (requestUser != null && requestUser.BaseInfo != null) { //OracleLogWriter.WriteQueryLog(BuildQueryLogStr(guideLineId, param, requestUser), Environment.TickCount - getQueryStartTime, count.ToString(), requestUser.BaseInfo.UserId, "2"); } } return(tb); }
/// <summary> /// 取所有主键的值 /// </summary> /// <param name="guideLineId"></param> /// <param name="param"></param> /// <param name="keyField"></param> /// <param name="requestUser"></param> /// <returns></returns> public static string GetAllKeyField(string guideLineId, Dictionary <string, object> param, string keyField, SinoRequestUser requestUser) { int count = 0; DataTable tb = new DataTable("ResultTable"); MD_GuideLine define = GetGuidelineDefine(guideLineId); if (define != null) { string queryStr = GetGuidelineMethod(guideLineId); List <MDQuery_GuideLineParameter> glPara = new List <MDQuery_GuideLineParameter>(); if (param != null && define.Parameters != null) { foreach (var p in param) { MD_GuideLineParameter md_pa = define.Parameters.Find(pa => pa.ParameterName == p.Key); if (md_pa != null) { glPara.Add(new MDQuery_GuideLineParameter(md_pa, p.Value)); } } } foreach (MDQuery_GuideLineParameter gp in glPara) { queryStr = OraQueryBuilder.RebuildGuideLineQueryString(queryStr, gp); } if (requestUser != null) { queryStr = OraQueryBuilder.ReplaceExtSecret(null, queryStr, requestUser); } tb = OraQueryModelHelper.FillResultData(queryStr, "ResultTable", ref count); try { tb.PrimaryKey = new DataColumn[] { tb.Columns[keyField] }; } catch { string errMsg = string.Format("取指标[zbid={0}]的结果集中所有主键[KeyField={1}]的值时设置主键出错,请检查主键是否唯一!", guideLineId, keyField); //OracleLogWriter.WriteSystemLog(errMsg, "ERROR"); return(""); } } List <string> listKeyField = new List <string>(); foreach (DataRow row in tb.Rows) { listKeyField.Add(row[keyField].ToString()); } return(string.Join(",", listKeyField)); }
/// <summary> /// 取指标定义 /// </summary> /// <param name="guideLineId"></param> /// <returns></returns> public static MD_GuideLine GetGuidelineDefine(string guideLineId) { MD_GuideLine define = null; if (!string.IsNullOrEmpty(guideLineId)) { if (GuidelineDefine.ContainsKey(guideLineId)) { define = GuidelineDefine[guideLineId]; } else { using (MySqlConnection cn = MysqlDBHelper.OpenConnection()) { try { MySqlCommand cmd = new MySqlCommand(SqlGetGuidelineDefine, cn); cmd.Parameters.Add(":Id", (MySqlDbType)decimal.Parse(guideLineId)); using (MySqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { string id = dr.IsDBNull(0) ? "" : dr.GetMySqlDecimal(0).Value.ToString(); string name = dr.IsDBNull(1) ? "" : dr.GetString(1); string groupname = dr.IsDBNull(2) ? "" : dr.GetString(2); string zbmeta1 = dr.IsDBNull(3) ? "" : dr.GetString(3); string fatherid = dr.IsDBNull(4) ? "0" : dr.GetMySqlDecimal(4).Value.ToString(); string zbmeta2 = dr.IsDBNull(5) ? "" : dr.GetString(5); int displayorder = dr.IsDBNull(6) ? 0 : Convert.ToInt32(dr.GetMySqlDecimal(6).Value); string descript = dr.IsDBNull(7) ? "" : dr.GetString(7); string fullMeta = zbmeta1 + zbmeta2; define = new MD_GuideLine(id, name, groupname, fatherid, displayorder, descript); //define.Parameters = MD_GuideLine.GetParametersFromMeta(fullMeta); //define.ResultGroups = MC_GuideLine.GetFieldGroupsFromMeta(fullMeta); //define.DetailDefines = MC_GuideLine.GetDetaiDefinelFromMeta(fullMeta); define.Children = GetChildGuidelineDefine(define.Id, cn); GuidelineDefine.Add(guideLineId, define); } } } catch (Exception exception) { string errorMessage = string.Format("取指标[{0}]的定义出错,错误信息为{1}", guideLineId, exception.Message); //OracleLogWriter.WriteSystemLog(errorMessage, "ERROR"); } } } } return(define); }
public static int GetQueryResultCount(string guideLineId, Dictionary <string, object> param, string filterWord, SinoRequestUser requestUser, MySqlConnection cn) { int ret = 0; string queryStr = GetGuidelineMethod(guideLineId); MD_GuideLine define = GetGuidelineDefine(guideLineId); if (define != null) { List <MDQuery_GuideLineParameter> glPara = new List <MDQuery_GuideLineParameter>(); if (param != null && define.Parameters != null) { foreach (var p in param) { MD_GuideLineParameter md_pa = define.Parameters.Find(pa => pa.ParameterName == p.Key); if (md_pa != null) { glPara.Add(new MDQuery_GuideLineParameter(md_pa, p.Value)); } } } foreach (MDQuery_GuideLineParameter gp in glPara) { queryStr = OraQueryBuilder.RebuildGuideLineQueryString(queryStr, gp); } if (requestUser != null) { queryStr = OraQueryBuilder.ReplaceExtSecret(null, queryStr, requestUser); } if (!string.IsNullOrEmpty(filterWord)) { queryStr = string.Format("select * from (\n {0} \n) where {1}", queryStr, filterWord); } try { ret = Convert.ToInt32(MysqlDBHelper.ExecuteScalar(cn, CommandType.Text, string.Format("select count(*) from (\n {0} \n) ", queryStr))); } catch (Exception exception) { string errorMessage = string.Format("取指标[{0}]的结果记录数出错,错误信息为{1}", guideLineId, exception.Message); //OracleLogWriter.WriteSystemLog(errorMessage, "ERROR"); } } return(ret); }
/// <summary> /// 取指标结果集 /// </summary> /// <param name="guideLineId"></param> /// <param name="param"></param> /// <param name="filterWord"></param> /// <param name="requestUser"></param> /// <returns></returns> public static DataTable QueryGuideline(string guideLineId, Dictionary <string, object> param, string filterWord, SinoRequestUser requestUser) { int getQueryStartTime = Environment.TickCount; int count = 0; DataTable tb = new DataTable("ResultTable"); MD_GuideLine define = GetGuidelineDefine(guideLineId); if (define != null) { string queryStr = GetGuidelineMethod(guideLineId); List <MDQuery_GuideLineParameter> glPara = new List <MDQuery_GuideLineParameter>(); if (param != null && define.Parameters != null) { foreach (var p in param) { MD_GuideLineParameter md_pa = define.Parameters.Find(pa => pa.ParameterName == p.Key); if (md_pa != null) { glPara.Add(new MDQuery_GuideLineParameter(md_pa, p.Value)); } } } foreach (MDQuery_GuideLineParameter gp in glPara) { queryStr = OraQueryBuilder.RebuildGuideLineQueryString(queryStr, gp); } if (requestUser != null) { queryStr = OraQueryBuilder.ReplaceExtSecret(null, queryStr, requestUser); } if (!string.IsNullOrEmpty(filterWord)) { queryStr = string.Format("select * from (\n {0} \n) where {1}", queryStr, filterWord); } tb = OraQueryModelHelper.FillResultData(queryStr, "ResultTable", ref count); if (requestUser != null && requestUser.BaseInfo != null) { //OracleLogWriter.WriteQueryLog(BuildQueryLogStr(guideLineId, param, requestUser), Environment.TickCount - getQueryStartTime, count.ToString(), requestUser.BaseInfo.UserId, "2"); } } return(tb); }
public static List <MD_GuideLine> GetChildGuidelineDefine(string guideLineId, MySqlConnection cn) { List <MD_GuideLine> _ret = new List <MD_GuideLine>(); MD_GuideLine define = null; if (!string.IsNullOrEmpty(guideLineId)) { try { MySqlCommand _cmd = new MySqlCommand(SqlGetChildGuidelineDefine, cn); //_cmd.Parameters.Add(":Id", decimal.Parse(guideLineId)); MySqlDataReader dr = _cmd.ExecuteReader(); while (dr.Read()) { string id = dr.IsDBNull(0) ? "" : dr.GetMySqlDecimal(0).Value.ToString(); string name = dr.IsDBNull(1) ? "" : dr.GetString(1); string groupname = dr.IsDBNull(2) ? "" : dr.GetString(2); string zbmeta1 = dr.IsDBNull(3) ? "" : dr.GetString(3); string fatherid = dr.IsDBNull(4) ? "0" : dr.GetMySqlDecimal(4).Value.ToString(); string zbmeta2 = dr.IsDBNull(5) ? "" : dr.GetString(5); int displayorder = dr.IsDBNull(6) ? 0 : Convert.ToInt32(dr.GetMySqlDecimal(6).Value); string descript = dr.IsDBNull(7) ? "" : dr.GetString(7); string fullMeta = zbmeta1 + zbmeta2; define = new MD_GuideLine(id, name, groupname, fatherid, displayorder, descript); //define.Parameters = MC_GuideLine.GetParametersFromMeta(fullMeta); //define.ResultGroups = MC_GuideLine.GetFieldGroupsFromMeta(fullMeta); //define.DetailDefines = MC_GuideLine.GetDetaiDefinelFromMeta(fullMeta); define.Children = GetChildGuidelineDefine(define.Id, cn); _ret.Add(define); } dr.Close(); } catch (Exception exception) { string errorMessage = string.Format("取GetChildGuidelineDefine子指标[{0}]的定义出错,错误信息为{1}", guideLineId, exception.Message); //OracleLogWriter.WriteSystemLog(errorMessage, "ERROR"); } } return(_ret); }