/// <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, string order)
{
if (where == null)
{
where = new List <KeyValue>();
}
string reportPath = CReport.GetReportPath(); //报表路径
if (string.IsNullOrEmpty(reportPath))
{
return(new DataSet());
}
string sqlCount = "";
string reportSql = CReport.GetSql(reportPath, reportName, where, ref sqlCount, 1).Replace("~", string.Empty); //获取要查询的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);
}
public static DataSet GetReportData(string sql, int pageSize, int pageIndex, string Order, ReportConnectionType type,
out int totalCount)
{
totalCount = 0;
if (pageIndex < 1) //不?能??出?现?索??引?y页?3小?于???1的??情??况?，??否??则??查??询??语??句?报???错???
{
return(new DataSet());
}
if (sql == null)
{
return(null);
}
sql = sql.Trim();
string conString = CReport.GetConnection(type); //获?取??SQL连??接??串??
if (string.IsNullOrEmpty(conString))
{
return(new DataSet());
}
var order = Order;
string rowOrder = "";
if (!string.IsNullOrEmpty(order))
{
if (order.ToUpper() == "NEWID()")
{ //取??随?机??行D数?y
order = "RAND()";
}
rowOrder = "order by " + order + "";
}
int start = pageSize * (pageIndex - 1);
int end = pageSize * pageIndex;
var match = Regex.Match(sql, @"\s+order\s+", RegexOptions.IgnoreCase); //检??查??语??句?中D是??否??含?有?Dorder by
string strCount = sql;
sql = string.Format(strCount + " " + rowOrder + " limit {0},{1}", start, pageSize);
strCount = "select count(0) from (" + strCount + ") item ";
sql = sql + ";" + strCount;
try
{
using (MySqlConnection conn = new MySqlConnection(conString))
{
MySqlCommand cmd = new MySqlCommand(sql, conn);
DataSet ds = new DataSet();
MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
adp.Fill(ds);
totalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
return(ds);
}
}
catch (Exception)
{
return(null);
}
}
/// <summary>
/// 导??出?报???表???
/// </summary>
/// <param name="reportSql"> 导??出?报???表???的??SQL </param>
/// <param name="where"> 过y滤?条??件t </param>
/// <param name="order"> 排?序??字??段? </param>
/// <returns></returns>
public static DataSet GetReportExportData(string reportName, List <KeyValue> where, int systemID,
ReportConnectionType type)
{
if (where == null)
{
where = new List <KeyValue>();
}
string reportPath = CReport.GetReportPath(systemID); //报表路??径?
if (string.IsNullOrEmpty(reportPath))
{
return(new DataSet());
}
string reportSql = CReport.GetSql(reportPath, reportName, where); //获?取??要?a查??询??的??SQL语??句? 及??其? 参?数?y
if (string.IsNullOrEmpty(reportSql))
{
return(new DataSet());
}
string conString = CReport.GetConnection(type); //获?取??SQL连??接??串??
if (string.IsNullOrEmpty(conString))
{
return(new DataSet());
}
string sql = reportSql;
//int count = CheckExportCount(sql, conString, where, "");
DataSet ds = new DataSet();
//for (int i = 0; i < Math.Ceiling((double)count / 9000); i++)
//{
// var sqlItem = string.Format(sql + " limit {0},{1}", i * 9000, 9000);
// if (ds.Tables.Count == 0)
// {
// ds = ExcuteMyDs(sqlItem, where, conString);
// }
// else
// {
// DataTable dt = ExcuteMyDs(sqlItem, where, conString).Tables[0];
// ds.Tables[0].Merge(dt);
// }
//}
//if (count == 0)
//{
ds = ExcuteMyDs(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.Uint());
}
/// <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)
{
string reportPath = CReport.GetReportPath(); //报表路径
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);
}
}
/// <summary>
/// 报???表???统?3计?
/// </summary>
/// <param name="reportName"> 对?应?|的??名?称? </param>
/// <param name="systemID"> 系??统?3ID</param>
/// <param name="where"> 查??询??条??件t </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, ReportConnectionType type, bool optimize = false)
{
string reportPath = CReport.GetReportPath(systemID); //报???表???路??径?
if (string.IsNullOrEmpty(reportPath))
{
return(new DataSet());
}
string sql = CReport.GetTotalSql(reportPath, reportName, where).Trim(); //获?取??要?a查??询??的??SQL语??句? 及??其? 参?数?y
if (string.IsNullOrEmpty(sql))
{
return(new DataSet());
}
string conString = CReport.GetConnection(type); //获取SQL连接串
if (string.IsNullOrEmpty(conString))
{
return(new DataSet());
}
DataSet ds = new DataSet();
using (MySqlConnection conn = new MySqlConnection(conString))
{
MySqlCommand cmd = new MySqlCommand(sql, conn);
//where 替换
foreach (var data in where)
{
cmd.Parameters.Add(new MySqlParameter("@" + data.Key, data.Value));
}
MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
adp.Fill(ds);
return(ds);
}
}
public static DataSet GetReportData(string sql, int pageSize, int pageIndex, string Order, 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, 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(); //报表路径
if (string.IsNullOrEmpty(reportPath))
{
return(new DataSet());
}
string sqlCount = "";
string sql = CReport.GetSql(reportPath, reportName, where, ref sqlCount, 1); //获取要查询的SQL语句 及其 参数
string[] sqlArray = sql.Split('~');
string orderBy = string.Empty;
if (sqlArray.Length > 1)
{
orderBy = sqlArray[1];
}
sql = sqlArray[0];
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 = string.IsNullOrEmpty(orderBy) ? "order by (select 0)" : orderBy;
}
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(";with cte as (select Row_Number() OVER ({3}) row , * from ({0})item) select * from cte WHERE cte.row BETWEEN {1} AND {2}", sql, start, end, rowOrder);
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;
//记录日志
//if (RunTime > 2000)
// Clients.LogClient.AddApplicationStartLogAsync(new U1City.ECDRP.Component.LogServices.ApplicationStartLog() { LogType = 1, Message = reportName, StartTime = DateTime.Now, RunTime = RunTime, SystemID = systemID });
return(ds);
}
}
public static DataSet GetReportData(string reportName, GetReportDataParams param, int systemID, ReportConnectionType type, 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) //不?能??出?现?索??引?y页?3小?于???1的??情??况?，??否??则??查??询??语??句?报???错???
{
pageIndex = 1;
}
if (where == null)
{
where = new List <KeyValue>();
}
string reportPath = CReport.GetReportPath(); //报???表???路??径?
if (string.IsNullOrEmpty(reportPath))
{
return(new DataSet());
}
string sql = CReport.GetSql(reportPath, reportName, where); //获?取??要?a查??询??的??SQL语??句? 及??其? 参?数?y
if (string.IsNullOrEmpty(sql))
{
return(new DataSet());
}
else
{
sql = sql.Trim();
}
string conString = CReport.GetConnection(type); //获取SQL连接串
if (string.IsNullOrEmpty(conString))
{
return(new DataSet());
}
string rowOrder = "";
if (!string.IsNullOrEmpty(order))
{
if (order.ToUpper() == "NEWID()")
{ //取??随?机??行D数?y
order = "RAND()";
}
rowOrder = "order by " + order + "";
}
int start = pageSize * (pageIndex - 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);
}
}
sql = string.Format(strCount + " " + rowOrder);
if (!optimize) //是否自定义分页
{
sql += string.Format(" limit {0},{1}", start, pageSize);
sql = sql.Insert(sql.ToLower().IndexOf("select") + 6, " SQL_CALC_FOUND_ROWS ");
sql = sql + ";" + "select FOUND_ROWS()";
}
else
{
sql = sql.Replace("@PagerLimit", string.Format(" limit {0},{1}", start, pageSize)); //针对特殊的分页，limit不是在sql的最后面的情况
}
var Rundate = DateTime.Now;
int RunTime = 0;
using (MySqlConnection conn = new MySqlConnection(conString))
{
MySqlCommand cmd = new MySqlCommand(sql, conn);
//where 替??换?
foreach (var data in where)
{
cmd.Parameters.Add(new MySqlParameter("@" + data.Key, data.Value));
}
DataSet ds = new DataSet();
MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
adp.Fill(ds);
totalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
RunTime = (int)DateTime.Now.Subtract(Rundate).TotalMilliseconds;
//记录日志
//if (RunTime > 2000)
//Clients.LogClient.AddApplicationStartLogAsync(new U1City.ECDRP.Component.LogServices.ApplicationStartLog() { LogType = 1, Message = reportName, StartTime = DateTime.Now, RunTime = RunTime, SystemID = systemID });
return(ds);
}
}