public static string ReplaceFunction(string _retstr) { object _retValue; string _res = _retstr; int _pos = _retstr.IndexOf('&'); if (_pos == -1 || _pos == (_retstr.Length - 1)) { return(_retstr); } int _pos2 = _retstr.IndexOf('&', _pos + 1); if (_pos2 == -1) { return(_retstr); } else { string _fun = _retstr.Substring(_pos, _pos2 - _pos + 1); string _sql = string.Format("select {0} RETVALUE from dual", _fun.Replace("&", "")); _retValue = MysqlDBHelper.ExecuteScalar(MysqlDBHelper.OpenConnection(), CommandType.Text, _sql, null);; if (_retValue != null) { _res = _retstr.Replace(_fun, _retValue.ToString()); } else { _res = _retstr.Replace(_fun, ""); } _res = ReplaceFunction(_res); } return(_res); }
/// <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> /// <returns></returns> public async static Task <IPagedList <DataTable> > QueryGuideline(string guideLineId, Dictionary <string, object> param, string filterWord, int pageIndex, int pageSize, string sortBy, string sortDirection) { int getQueryStartTime = Environment.TickCount; int recordCount = 0; DataTable tb = new DataTable("ResultTable"); MD_GuideLine define = await GetGuidelineDefine(guideLineId); if (define != null) { string queryStr = await 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 = MySqlQueryBuilder.RebuildGuideLineQueryString(queryStr, gp); } if (!string.IsNullOrEmpty(filterWord)) { queryStr = string.Format("select * from (\n {0} \n) where {1}", queryStr, filterWord); } try { var cot = await MysqlDBHelper.ExecuteScalar(MysqlDBHelper.queryString, CommandType.Text, string.Format("select count(*) from (\n {0} \n) as t ", queryStr)); recordCount = Convert.ToInt32(cot); } catch (Exception e) { MysqlLogWriter.WriteSystemLog("Exception :QueryGuideline310行异常,异常信息为" + e.Message, "ERROR"); } queryStr = StrUtils.BuildPagingSQL22(queryStr, pageIndex, pageSize, sortBy, sortDirection); tb = await FillResultData(queryStr, "ResultTable"); MysqlLogWriter.WriteQueryLog(queryStr, Environment.TickCount - getQueryStartTime, "2"); } var list = new List <DataTable>(); list.Add(tb); var ret = new StaticPagedList <DataTable>(list, pageIndex, pageSize, recordCount); return(ret); }
/// <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); }
//用于生成代码表的子代码的字符串集合 private static async Task <string> GetRefTableChildrenSet(string refDMB, string code) { MySqlParameter[] _param = { new MySqlParameter("@strdmb", MySqlDbType.VarChar, 20), new MySqlParameter("@strcode", MySqlDbType.VarChar, 20) }; _param[0].Value = refDMB; _param[1].Value = code; object _ret = await MysqlDBHelper.ExecuteScalar(MysqlDBHelper.conf, CommandType.Text, SQL_GetRefTableChildrenSet, _param); return((_ret == null) ? "" : _ret.ToString()); }
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> /// 取指标的sql语句 /// </summary> /// <param name="guideLineId"></param> /// <returns></returns> public static string GetGuidelineMethod(string guideLineId) { MySqlParameter[] param = { new MySqlParameter(":Id", MySqlDbType.Decimal) }; try { param[0].Value = decimal.Parse(guideLineId); object sfobj = MysqlDBHelper.ExecuteScalar(MysqlDBHelper.conf, CommandType.Text, SqlGetGuideLineMethod, param); if (sfobj == null || sfobj == DBNull.Value) { return(""); } return(sfobj.ToString()); } catch (Exception e) { string errorMessage = string.Format("取指标[{0}]的sql语句出错,错误信息为{1}", guideLineId, e.Message); //OracleLogWriter.WriteSystemLog(errorMessage, "ERROR"); return(""); } }
/// <summary> /// 取指标的sql语句 /// </summary> /// <param name="guideLineId"></param> /// <returns></returns> public async static Task <string> GetGuidelineMethod(string guideLineId) { try { MySqlParameter[] param = { new MySqlParameter("@Id", MySqlDbType.Int64) }; param[0].Value = Convert.ToInt64(guideLineId); object sfobj = await MysqlDBHelper.ExecuteScalar(MysqlDBHelper.conf, CommandType.Text, SqlGetGuideLineMethod, param); if (sfobj == null || sfobj == DBNull.Value) { return(""); } return(sfobj.ToString()); } catch (Exception e) { string errorMessage = string.Format("取指标[{0}]的sql语句出错,错误信息为{1}", guideLineId, e.Message); MysqlLogWriter.WriteSystemLog(errorMessage, "ERROR"); return(""); } }