/// <summary>
/// 导出报表
/// </summary>
/// <param name="reportSql">导出报表的SQL</param>
/// <param name="where">过滤条件</param>
/// <param name="order">排序字段</param>
/// <returns></returns>
public static DataSet GetReportExportData(string reportName, List <KeyValue> where, int systemID, string order, ReportConnectionType type)
{
if (where == null)
{
where = new List <KeyValue>();
}
string reportPath = CReport.GetReportPath(systemID); //报表路径
if (string.IsNullOrEmpty(reportPath))
{
return(new DataSet());
}
string sqlCount = "";
string reportSql = CReport.GetSql(reportPath, reportName, where, 1); //获取要查询的SQL语句 及其 参数
if (string.IsNullOrEmpty(reportSql))
{
return(new DataSet());
}
string conString = CReport.GetConnection(); //获取SQL连接串
if (string.IsNullOrEmpty(conString))
{
return(new DataSet());
}
string sql = reportSql;
int count = CheckExportCount(sql, conString, where, "");
DataSet ds = new DataSet();
ds = ExcuteMSDs(sql, where, conString);
//MonitorManager.SetReturnValue(string.Format("Excel导出{0}条", count));
return(ds);
}
//判断导出的行数
private static int CheckExportCount(string sql, string conString, List <KeyValue> where, string order)
{
string rowOrder = string.Empty;
var matchs = Regex.Matches(sql, @"\s+order\s+", RegexOptions.IgnoreCase); //检查语句中是否含有order by
string strCount = sql;
if (matchs.Count > 1)
{
strCount = sql.Substring(0, matchs[matchs.Count - 1].Index);
if (string.IsNullOrEmpty(order))
{
rowOrder = sql.Substring(matchs[matchs.Count - 1].Index);
}
}
else if (matchs.Count == 1)
{
strCount = sql.Substring(0, matchs[0].Index);
if (string.IsNullOrEmpty(order))
{
rowOrder = sql.Substring(matchs[0].Index);
}
}
strCount = CReport.GetStrCount(strCount);
DataSet ds = new DataSet();
using (SqlConnection conn = new SqlConnection(conString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
//where 替换
foreach (var data in where)
{
cmd.Parameters.Add(new SqlParameter("@" + data.Key, data.Value));
}
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds);
}
if (ds.Tables.Count < 1 || ds.Tables[0].Rows.Count < 1)
{
return(-1);
}
return(ds.Tables[0].Rows.Count);
}
/// <summary>
/// 报表统计
/// </summary>
/// <param name="reportName">对应的名称</param>
/// <param name="systemID">系统ID</param>
/// <param name="where">查询条件</param>
/// <param name="type">报表路径</param>
/// <param name="Totalstartsql">Sql头(select order ,count(*) as total from )</param>
/// <param name="Totalendsql">Sql(group by order)</param>
/// <returns></returns>
public static DataSet GetReportTotal(string reportName, List <KeyValue> where, int systemID)
{
string reportPath = CReport.GetReportPath(systemID); //报表路径
if (string.IsNullOrEmpty(reportPath))
{
return(new DataSet());
}
string sql = CReport.GetTotalSql(reportPath, reportName, where, 1).Trim(); //获取要查询的SQL语句 及其 参数
if (string.IsNullOrEmpty(sql))
{
return(new DataSet());
}
string conString = CReport.GetConnection(); //获取SQL连接串
if (string.IsNullOrEmpty(conString))
{
return(new DataSet());
}
using (SqlConnection conn = new SqlConnection(conString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
//where 替换
foreach (var data in where)
{
cmd.Parameters.Add(new SqlParameter("@" + data.Key, data.Value));
}
DataSet ds = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(ds);
return(ds);
}
}
public static DataSet GetReportData(string sql, int pageSize, int pageIndex, string Order, ReportConnectionType type, out int totalCount)
{
string order = null;
totalCount = 0;
if (pageIndex < 1) //不能出现索引页小于1的情况，否则查询语句报错
{
return(new DataSet());
}
string conString = CReport.GetConnection(); //获取SQL连接串
if (string.IsNullOrEmpty(conString))
{
return(new DataSet());
}
string rowOrder = Order;
if (!string.IsNullOrEmpty(order))
{
rowOrder = "order by " + order + "";
}
else
{
rowOrder = "order by (select 0)";
}
int start = pageSize * (pageIndex - 1) + 1;
int end = pageSize * pageIndex;
var match = Regex.Match(sql, @"\s+order\s+", RegexOptions.IgnoreCase); //检查语句中是否含有order by
string strCount = sql;
if (match.Success) //有order by 则舍去order by
{
strCount = sql.Substring(0, match.Index);
if (string.IsNullOrEmpty(order))
{
rowOrder = sql.Substring(match.Index);
}
}
sql = @" SELECT * FROM ( SELECT Row_Number() OVER ({0}) row, * from ( select * FROM (" + strCount + " ) tt) t ) item "
+ " WHERE item.row BETWEEN " + start + " AND " + end + " ";
sql = string.Format(sql, rowOrder);
strCount = "select count(0) from (" + strCount + ") item ";
sql = sql + ";" + strCount;
try
{
using (SqlConnection conn = new SqlConnection(conString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
DataSet ds = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(ds);
totalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
return(ds);
}
}
catch (Exception)
{
return(null);
}
}
public static DataSet GetReportData(string reportName, GetReportDataParams param, int systemID, out int totalCount, bool optimize = false)
{
int pageSize = param.PageSize;
int pageIndex = param.PageIndex;
string order = param.Order;
List <KeyValue> where = param.Where;
totalCount = 0;
if (pageIndex < 1) //不能出现索引页小于1的情况，否则查询语句报错
{
return(new DataSet());
}
if (where == null)
{
where = new List <KeyValue>();
}
string reportPath = CReport.GetReportPath(systemID); //报表路径
if (string.IsNullOrEmpty(reportPath))
{
return(new DataSet());
}
string sqlCount = "";
string sql = CReport.GetSql(reportPath, reportName, where, 1); //获取要查询的SQL语句 及其 参数
if (string.IsNullOrEmpty(sql))
{
return(new DataSet());
}
else
{
sql = sql.Trim();
}
string conString = CReport.GetConnection(); //获取SQL连接串
if (string.IsNullOrEmpty(conString))
{
return(new DataSet());
}
string rowOrder = "";
if (!string.IsNullOrEmpty(order))
{
rowOrder = "order by " + order + "";
}
else
{
rowOrder = "order by (select 0)";
}
int start = pageSize * (pageIndex - 1) + 1;
int end = pageSize * pageIndex;
var matchs = Regex.Matches(sql, @"\s+order\s+", RegexOptions.IgnoreCase); //检查语句中是否含有order by
string strCount = sql;
if (matchs.Count > 1)
{
strCount = sql.Substring(0, matchs[matchs.Count - 1].Index);
if (string.IsNullOrEmpty(order))
{
rowOrder = sql.Substring(matchs[matchs.Count - 1].Index);
}
}
else if (matchs.Count == 1)
{
strCount = sql.Substring(0, matchs[0].Index);
if (string.IsNullOrEmpty(order))
{
rowOrder = sql.Substring(matchs[0].Index);
}
}
int firstSelectIndex = Regex.Matches(strCount, @"select", RegexOptions.IgnoreCase)[0].Index + 7;
sql = strCount.Insert(firstSelectIndex, " Row_Number() OVER ({0}) row ,");
sql = string.Format(sql, rowOrder);
sql = string.Format("select * from ( {0} ) item WHERE item.row BETWEEN {1} AND {2}", sql, start, end);
if (string.IsNullOrEmpty(sqlCount))
{
sqlCount = "select count(0) from (" + strCount + ") item ";
}
sql = sql + ";" + sqlCount;
var Rundate = DateTime.Now;
int RunTime = 0;
using (SqlConnection conn = new SqlConnection(conString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
//where 替换
foreach (var data in where)
{
cmd.Parameters.Add(new SqlParameter("@" + data.Key, data.Value));
}
DataSet ds = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(ds);
if (ds.Tables.Count > 1)
{
totalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
}
else
{
totalCount = 0;
}
conn.Close();
RunTime = (int)DateTime.Now.Subtract(Rundate).TotalMilliseconds;
return(ds);
}
}
