const string PARAM_BETWEEN_BOTH_PARAM_NULL = @"\b\w+\s+between\s+null\s+and\s+null\b"; //匹配between 两侧侧参数为null
/// <summary>
/// 执行报表
/// </summary>
/// <param name="id">报表ID</param>
/// <param name="request">请求信息</param>
/// <returns></returns>
public static int QueryReport(ReportRequest request, out ReportResult result, out string msg)
{
try
{
#region 授权检测
Syscfg cfg = SyscfgBLL.LoadCfg(out msg);
if (cfg == null)
{
msg = "系统参数错误，请联系管理员";
result = null;
return(-99);
}
if (cfg.ExpDate == "无效" || (cfg.ExpDate != "永久" && DateTime.Now.CompareTo(DateTime.Parse(cfg.ExpDate)) > 0))
{
//过期
msg = "授权已过期";
result = null;
return(-99);
}
int rptCount;
if (cfg.ReportNumber == "无效" || !int.TryParse(cfg.ReportNumber, out rptCount))
{
throw new Exception("报表授权数获取失败");
}
if (rptCount < ReportCount())
{
//超过报表数
msg = "报表数已超过授权数量";
result = null;
return(-99);
}
#endregion
Report rpt = GetReport(request.ReportID);//获取报表
result = new ReportResult()
{
TotalCount = 0,
CurPage = request.Page,
PageSize = rpt.PageSize
};
string connectionString;
short dbType;
int i = -1;
i = DatabaseBLL.GetConnectionString(rpt.DBID, out connectionString, out dbType, out msg);
if (i != 1)
{
//获取数据库连接参数失败
result = null;
return(i);
}
//开始组装sql
StringBuilder sql = new StringBuilder(256);
sql.Append(rpt.SqlCommand);
var orderBy = new StringBuilder();
if (!rpt.CommandHasOrderby && !string.IsNullOrEmpty(request.SortColumn))
{
//排序请求
orderBy.AppendFormat(" Order By {0} {1}",
request.SortColumn,
request.Desc?"Desc":string.Empty
);
}
string finalSql = sql.ToString();
DataTable rstTable;
using (IDAL dal = DALBuilder.CreateDAL(connectionString, dbType))
{
//组成parameter
List <IDbDataParameter> pList = new List <IDbDataParameter>();
if (request.Params != null)
{
//替换所有的
for (int index = 0; index < request.Params.Length; index++)
{
if (request.Params[index].ParamValue == null)
{
//将所有值为null的参数 替换为null
finalSql = Regex.Replace(finalSql, PARAM_REGEX.Replace("参数名", request.Params[index].ParamCode), " null ");
}
}
finalSql = Regex.Replace(finalSql, PARAM_SIG_OPERATOR, " 1=1 "); //将所有 参数【单操作符】null 的项 替换为1=1
finalSql = Regex.Replace(finalSql, PARAM_MUL_OPERATOR, " 1=1 "); //将所有参数【多操作符】null 的项替换为1=1
finalSql = Regex.Replace(finalSql, PARAM_LIKE, " 1=1 "); //将所有like null的项替换为1=1
finalSql = Regex.Replace(finalSql, PARAM_BETWEEN_BOTH_PARAM_NULL, "1=1"); //将所有between null and null 的项替换为1=1
MatchCollection mc = Regex.Matches(finalSql, PARAM_BETWEEN_LEFT_PARAM_NULL); //匹配between null and 参数 的项
if (mc.Count > 0)
{
foreach (Match m in mc)
{
string tmp = " <= " + (dbType == 0 ? "@" : ":") + Regex.Match(m.Value, @"(?<=@)\w+\b").Value + " ";
finalSql = finalSql.Replace(m.Value, tmp);
}
}
mc = Regex.Matches(finalSql, PARAM_BETWEEN_RIGHT_PARAM_NULL);//匹配between 参数 and null
if (mc.Count > 0)
{
foreach (Match m in mc)
{
string tmp = " >=" + (dbType == 0 ? "@" : ":") + Regex.Match(m.Value, @"(?<=@)\w+\b").Value + " ";
finalSql = finalSql.Replace(m.Value, tmp);
}
}
foreach (ReportParam p in request.Params)
{
IDbDataParameter dbp = null;
if (p.ParamValue == null)
{
continue;//值为null的参数不加入参数列表
}
switch (p.ParamType)
{
case 0:
dbp = dal.CreateParameter(p.ParamCode, DbType.String);
dbp.Value = string.IsNullOrEmpty(p.ParamValue) ? string.Empty : p.ParamValue;
break;
case 1:
decimal v;
dbp = dal.CreateParameter(p.ParamCode, DbType.Decimal);
if (decimal.TryParse(p.ParamValue, out v))
{
dbp.Value = v;
}
else
{
dbp.Value = null;
}
break;
case 2:
DateTime d;
dbp = dal.CreateParameter(p.ParamCode, DbType.DateTime);
if (DateTime.TryParse(p.ParamValue, out d))
{
dbp.Value = d;
}
else
{
dbp.Value = null;
}
break;
}
if (dbp == null)
{
throw new Exception("参数错误");
}
pList.Add(dbp);
}
}
if (rpt.Pagingabled)
{
//分页请求
rstTable = dal.Select(finalSql.Replace('\r', ' ').Replace('\n', ' ') + orderBy.ToString(), rpt.PageSize * (request.Page - 1), rpt.PageSize, out i, pList.ToArray());
}
else
{
rstTable = dal.Select(finalSql.Replace('\r', ' ').Replace('\n', ' ') + orderBy.ToString(), out i, pList.ToArray());
}
if (rpt.PageSumabled)
{
//页合计请求
DataRow row = rstTable.NewRow();
foreach (ReportColumn c in rpt.Columns)
{
if (!c.Sumabled)
{
continue;
}
row[c.ColumnCode] = rstTable.Compute("sum(" + c.ColumnCode + ")", "");
}
rstTable.Rows.InsertAt(row, rstTable.Rows.Count);
}
sql.Clear();
sql.Append(" Select ");
if (rpt.AllSumabled)
{
//总合计请求
foreach (ReportColumn c in rpt.Columns)
{
if (c.Sumabled)
{
sql.AppendFormat(" Sum({0}) AS {0}, ",
c.ColumnCode
);
}
else
{
sql.AppendFormat(" null AS {0}, ",
c.ColumnCode
);
}
}
}
sql.Append(" Count(*) AS TotalCount ");
sql.AppendFormat(" From {0} ", Regex.Match(finalSql.Replace('\r', ' ').Replace('\n', ' '), ALLSUM_FROM_REGEX, RegexOptions.IgnoreCase).Value);
IDbDataParameter[] pList2 = new IDbDataParameter[pList.Count];
for (int j = 0; j < pList.Count; j++)
{
pList2[j] = dal.CloneParameter(pList[j]);
}
dal.OpenReader(sql.ToString(), pList2);
if (dal.DataReader.Read() && rpt.AllSumabled)
{
DataRow newrow = rstTable.NewRow();
foreach (ReportColumn c in rpt.Columns)
{
if (!c.Sumabled)
{
continue;
}
newrow[c.ColumnCode] = dal.DataReader[c.ColumnCode];
}
rstTable.Rows.Add(newrow);
}
result.TotalCount = Convert.ToInt32(dal.DataReader["TotalCount"]);
dal.DataReader.Close();
}
result.ReportData = JsonHelper.DatatableToJson(rstTable);
return(1);
}
catch (Exception ex)
{
result = null;
msg = ex.Message;
return(-1);
}
}
