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("");
}
}