/// <summary>
/// 前??台???需??要?a点??击??排?序??的??时???候??调???用??
/// </summary>
/// <param name="reportName"> XML对?应?|的??名?称? </param>
/// <param name="where"> 查??询??条??件t </param>
/// <param name="pageSize"> 页?3面?打???小? </param>
/// <param name="pageIndex"> 页?3面?索??引?y </param>
/// <param name="order"></param>
/// <param name="systemID"> 系??统?3ID</param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static DataSet GetReportData(string reportName, GetReportDataParams param,
int systemID, ReportConnectionType type, out int totalCount, bool optimize = false)
{
if (param.Where != null)
{
foreach (var item in param.Where)
{
item.Value = item.Value.Trim();
}
}
DataSet ds;
//switch (ConfigContext.SqlAss)
//{
// case SqlEnum.MSSql:
// ds = MSSqlHelper.GetReportData(reportName, param, systemID, type, out totalCount);
// break;
// case SqlEnum.MYSql:
// ds = MySqlHelper.GetReportData(reportName, param, systemID, type, out totalCount, optimize);
// if (ds.Tables[0].Columns.Cast<DataColumn>().Where(q => q.DataType == typeof(Int64)).Count() == 0)
// {
// break;
// }
// DataSet resDs = new DataSet();
// resDs.Tables.Add(ds.Tables[0].Clone());
// resDs.Tables[0].Columns.Cast<DataColumn>().Where(q => q.DataType == typeof(Int64)).ToList().ForEach(q => q.DataType = typeof(int));
// foreach (var item in ds.Tables[0].Rows)
// {
// resDs.Tables[0].ImportRow(item as DataRow);
// }
// ds = resDs;
// break;
// default:
// ds = MSSqlHelper.GetReportData(reportName, param, systemID, type, out totalCount);
// break;
//}
ds = MySqlHelper.GetReportData(reportName, param, systemID, type, out totalCount, optimize);
if (ds.Tables[0].Columns.Cast <DataColumn>().Where(q => q.DataType == typeof(Int64)).Count() == 0)
{
return(ds);
}
DataSet resDs = new DataSet();
resDs.Tables.Add(ds.Tables[0].Clone());
resDs.Tables[0].Columns.Cast <DataColumn>().Where(q => q.DataType == typeof(Int64)).ToList().ForEach(q => q.DataType = typeof(int));
foreach (var item in ds.Tables[0].Rows)
{
resDs.Tables[0].ImportRow(item as DataRow);
}
ds = resDs;
return(ds);
}
/// <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);
}
/// <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)
{
foreach (var item in where)
{
item.Value = item.Value.Trim();
}
}
//switch (ConfigContext.SqlAss)
//{
// case SqlEnum.MSSql:
// return MSSqlHelper.GetReportExportData(reportName, where, systemID, "", type);
// case SqlEnum.MYSql:
// {
// DataSet ds = MySqlHelper.GetReportExportData(reportName, where, systemID, "", type);
// if (ds.Tables[0].Columns.Cast<DataColumn>().Where(q => q.DataType == typeof(Int64)).Count() == 0)
// return ds;
// DataSet resDs = new DataSet();
// resDs.Tables.Add(ds.Tables[0].Clone());
// resDs.Tables[0].Columns.Cast<DataColumn>().Where(q => q.DataType == typeof(Int64)).ToList().ForEach(q => q.DataType = typeof(int));
// foreach (var item in ds.Tables[0].Rows)
// {
// resDs.Tables[0].ImportRow(item as DataRow);
// }
// return resDs;
// }
// default:
// return MSSqlHelper.GetReportExportData(reportName, where, systemID, "", type);
//}
DataSet ds = MySqlHelper.GetReportExportData(reportName, where, systemID, type);
if (ds.Tables[0].Columns.Cast <DataColumn>().Where(q => q.DataType == typeof(Int64)).Count() == 0)
{
return(ds);
}
DataSet resDs = new DataSet();
resDs.Tables.Add(ds.Tables[0].Clone());
resDs.Tables[0].Columns.Cast <DataColumn>().Where(q => q.DataType == typeof(Int64)).ToList().ForEach(q => q.DataType = typeof(int));
foreach (var item in ds.Tables[0].Rows)
{
resDs.Tables[0].ImportRow(item as DataRow);
}
return(resDs);
}
/// <summary>
/// 报???表???管??理???查??询??语??句?
/// </summary>
/// <param name="sql"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="type"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static DataSet GetReportData(string sql, int pageSize, int pageIndex, string Order,
ReportConnectionType type, out int totalCount)
{
//switch (ConfigContext.SqlAss)
//{
// case SqlEnum.MSSql:
// return MSSqlHelper.GetReportData(sql, pageSize, pageIndex, Order, type, out totalCount);
// case SqlEnum.MYSql:
// {
// DataSet ds = MySqlHelper.GetReportData(sql, pageSize, pageIndex, Order, type, out totalCount);
// if (ds == null || ds.Tables.Count == 0)
// return null;
// if (ds.Tables[0].Columns.Cast<DataColumn>().Where(q => q.DataType == typeof(Int64)).Count() == 0)
// return ds;
// DataSet resDs = new DataSet();
// resDs.Tables.Add(ds.Tables[0].Clone());
// resDs.Tables[0].Columns.Cast<DataColumn>().Where(q => q.DataType == typeof(Int64)).ToList().ForEach(q => q.DataType = typeof(int));
// foreach (var item in ds.Tables[0].Rows)
// {
// resDs.Tables[0].ImportRow(item as DataRow);
// }
// return resDs;
// }
// default:
// return MSSqlHelper.GetReportData(sql, pageSize, pageIndex, Order, type, out totalCount);
//}
DataSet ds = MySqlHelper.GetReportData(sql, pageSize, pageIndex, Order, type, out totalCount);
if (ds == null || ds.Tables.Count == 0)
{
return(null);
}
if (ds.Tables[0].Columns.Cast <DataColumn>().Where(q => q.DataType == typeof(Int64)).Count() == 0)
{
return(ds);
}
DataSet resDs = new DataSet();
resDs.Tables.Add(ds.Tables[0].Clone());
resDs.Tables[0].Columns.Cast <DataColumn>().Where(q => q.DataType == typeof(Int64)).ToList().ForEach(q => q.DataType = typeof(int));
foreach (var item in ds.Tables[0].Rows)
{
resDs.Tables[0].ImportRow(item as DataRow);
}
return(resDs);
}
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);
}
}
/// <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);
}
/// <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, 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);
}
}
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);
}
}
