/// <summary>
/// 重建报表结构
/// </summary>
/// <param name="id"></param>
/// <param name="SQL"></param>
/// <param name="msg"></param>
/// <returns></returns>
public static Report RebuildReport(decimal id, decimal dbID, string SQL, out string msg)
{
Report report, reportdb;
report = new Report();
report.Columns = new List <ReportColumn>();
report.Params = new List <ReportParam>();
report.SqlCommand = SQL;
string tmpSql = Regex.Replace(SQL, REGEX_PARAMS_2, "NULL");//将所有参数设置为null，获取表结构
try
{
string tarConn;
short dbType;
if (DatabaseBLL.GetConnectionString(dbID, out tarConn, out dbType, out msg) != 1)
{
throw new Exception(msg);
}
using (IDAL dal = DALBuilder.CreateDAL(tarConn, dbType))
{
//获取SQL语句中的Column
DataSet ds = dal.Select(tmpSql.Replace('\r', ' ').Replace('\n', ' '));//替换掉回车、换行符
if (ds.Tables.Count != 1)
{
throw new Exception("错误：查询结果必须只有一个结果表");
}
else
{
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
ReportColumn column = new ReportColumn()
{
ReportID = id,
ColumnCode = ds.Tables[0].Columns[i].ColumnName,
OrderIndex = i + 1
};
switch (ds.Tables[0].Columns[i].DataType.Name)
{
case "Int16":
case "Int32":
case "Int64":
case "Decimal":
case "Float":
column.ColumnType = 1;
break;
case "String":
column.ColumnType = 0;
break;
case "Datetime":
column.ColumnType = 2;
break;
default:
column.ColumnType = 0;
break;
}
report.Columns.Add(column);
}
}
}
//获取SQL语句中的Param
MatchCollection pms = Regex.Matches(SQL, REGEX_PARAMS);
foreach (Match m in pms)
{
//获取SQL语句中的参数
report.Params.Add(new ReportParam()
{
ReportID = id,
ParamCode = m.Value
});
}
if (id != 0)
{
//获取到数据库report
report.ID = id;
reportdb = GetReport(id);
report.Enabled = reportdb.Enabled;
//两个report进行比较
//比较params
foreach (ReportParam rp in report.Params)
{
IEnumerable <ReportParam> dbrps = reportdb.Params.Where(p => p.ParamCode == rp.ParamCode);
if (dbrps.Count() == 0)
{
continue;
}
ReportParam dbrp = dbrps.First();
rp.ParamInputType = dbrp.ParamInputType;
rp.ParamItems = dbrp.ParamItems;
rp.ParamName = dbrp.ParamName;
rp.ParamType = dbrp.ParamType;
}
//比较columns
foreach (ReportColumn rc in report.Columns)
{
IEnumerable <ReportColumn> dbclms = reportdb.Columns.Where(c => c.ColumnCode == rc.ColumnCode);
if (dbclms.Count() == 0)
{
//数据库中原本不存在
continue;
}
ReportColumn dbclm = dbclms.First();
rc.ColumnName = dbclm.ColumnName;
rc.Sortabled = report.CommandHasOrderby?false:dbclm.Sortabled;//如果含有OrderBy 则不允许排序
rc.Sumabled = dbclm.Sumabled;
}
}
msg = "success";
return(report);
}
catch (Exception ex)
{
msg = ex.Message;
return(null);
}
}
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);
}
}