1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using System.Xml.Linq;
6 using System.Text.RegularExpressions;
7 using System.Data.SqlClient;
8 using System.Data;
9 using System.Configuration;
10 using System.Xml;
11
12 namespace Components
13 {
14 public static class ReportHelper
15 {
16 #region 获取分页数据
17 /// <summary>
18 /// 获取分页数据
19 /// </summary>
20 /// <typeparam name="T"></typeparam>
21 /// <param name="reportName"></param>
22 /// <param name="p_Params"></param>
23 /// <param name="p_ReportXmlName"></param>
24 /// <param name="totalCount"></param>
25 /// <returns></returns>
26 public static List<T> GetPageData<T>(
string reportName, ReportParams p_Params,
string p_ReportXmlName,
out int totalCount)
27 {
28 List<T> m_List =
new List<T>
();
29 DataSet m_result = getReportDataByPage(reportName, p_Params, p_ReportXmlName,
out totalCount);
30 if (!
m_result.IsEmpty())
31 {
32 m_List = m_result.Tables[
0].ToList<T>
();
33 }
34 return m_List;
35 }
36
37 /// <summary>
38 /// 获取分页数据
39 /// </summary>
40 /// <typeparam name="T"></typeparam>
41 /// <param name="reportName"></param>
42 /// <param name="p_Params"></param>
43 /// <param name="p_ReportXmlName"></param>
44 /// <param name="totalCount"></param>
45 /// <returns>返回DataTable</returns>
46 public static DataTable GetDataTablePageData(
string reportName, ReportParams p_Params,
string p_ReportXmlName,
out int totalCount)
47 {
48 DataSet m_result = getReportDataByPage(reportName, p_Params, p_ReportXmlName,
out totalCount);
49 if (!
m_result.IsEmpty())
50 {
51 return m_result.Tables[
0];
52 }
53 return null;
54 }
55 /// <summary>
56 /// 获取不分页数据
57 /// </summary>
58 /// <param name="reportName"></param>
59 /// <param name="p_Params"></param>
60 /// <param name="p_ReportXmlName"></param>
61 /// <param name="totalCount"></param>
62 /// <returns></returns>
63 public static DataTable GetDataTableData(
string reportName, ReportParams p_Params,
string p_ReportXmlName)
64 {
65 return getData(reportName, p_Params, p_ReportXmlName);
66 }
67 /// <summary>
68 /// 获取不分页数据
69 /// </summary>
70 /// <typeparam name="T"></typeparam>
71 /// <param name="reportName"></param>
72 /// <param name="p_Params"></param>
73 /// <param name="p_ReportXmlName"></param>
74 /// <returns></returns>
75 public static List<T> GetDataListData<T>(
string reportName, ReportParams p_Params,
string p_ReportXmlName)
76 {
77 List<T> m_List =
new List<T>
();
78 DataTable m_result =
getData(reportName, p_Params, p_ReportXmlName);
79 if (!
m_result.IsEmpty())
80 {
81 m_List = m_result.ToList<T>
();
82 }
83 return m_List;
84 }
85 #endregion
86
87 #region 导出报表
88 /// <summary>
89 /// 获取分页数据
90 /// </summary>
91 /// <typeparam name="T"></typeparam>
92 /// <param name="reportName"></param>
93 /// <param name="p_Params"></param>
94 /// <param name="p_ReportXmlName"></param>
95 /// <returns></returns>
96 public static List<T> GetExportData<T>(
string reportName,
string p_ReportXmlName, ReportParams p_Params,
out int totalCount)
97 {
98 totalCount =
0;
99 List<T> m_List =
new List<T>
();
100 DataTable m_result =
getData(reportName, p_Params, p_ReportXmlName);
101 //DataSet m_result = getReportExportData(reportName, p_ReportXmlName, p_Params.Where);
102 if (!
m_result.IsEmpty())
103 {
104 m_List = m_result.ToList<T>
();
105 }
106 return m_List;
107 }
108
109 #endregion
110
111 #region 前台需要点击排序的时候调用
112 /// <summary>
113 /// 要获取数据的XML配置报表名称
114 /// </summary>
115 /// <param name="reportName">要获取数据的XML配置报表名称</param>
116 /// <param name="p_Params">报表参数</param>
117 /// <param name="p_ReportXmlName">报表XML文件名称</param>
118 /// <param name="totalCount">数据总数</param>
119 /// <returns></returns>
120 private static DataSet getReportDataByPage(
string reportName, ReportParams p_Params,
string p_ReportXmlName,
out int totalCount)
121 {
122 List<KeyValue>
where =
p_Params.Where;
123 int pageSize =
p_Params.PageSize;
124 int pageIndex =
p_Params.PageIndex;
125 string order =
p_Params.Order;
126 string sort =
p_Params.Sort;
127 totalCount =
0;
128 if (pageIndex <
1)
//不能出现索引页小于1的情况，否则查询语句报错
129 return null;
130 if (
where ==
null)
131 where =
new List<KeyValue>
();
132
133 string reportPath = GetReportPath(p_ReportXmlName);
//报表路径
134
135 if (
string.IsNullOrEmpty(reportPath))
136 return null;
137
138 string sql = GetSql(reportPath, reportName,
where).Trim();
//获取要查询的SQL语句 及其 参数
139 if (
string.IsNullOrEmpty(sql))
140 return null;
141
142 string conString = GetConnection();
//获取SQL连接串
143
144 if (
string.IsNullOrEmpty(conString))
145 return null;
146 string rowOrder =
"";
147 if (!
string.IsNullOrEmpty(order))
148 {
149 rowOrder =
"order by "+sort+
" " + order+
" ";
150 }
151 else
152 {
153 rowOrder =
"order by t.Id desc";
154 }
155 int start = pageSize * (pageIndex -
1) +
1;
156 int end = pageSize *
pageIndex;
157
158
159 var match = Regex.Match(sql,
@"\s+order\s+", RegexOptions.IgnoreCase);
//检查语句中是否含有order by
160 string strCount =
sql;
161 if (match.Success)
//有order by 则舍去order by
162 {
163 strCount = sql.Substring(
0, match.Index);
164 if (
string.IsNullOrEmpty(order))
165 {
166 rowOrder =
sql.Substring(match.Index);
167 }
168
169 sql = ForSql(strCount, rowOrder, start, end);
//把order by 加入到rownumber
170 }
171 else
172 {
173 strCount =
sql;
174 sql =
@" SELECT * FROM ( SELECT Row_Number() OVER ({0}) row, * from ( select * FROM (" + sql +
" ) tt) t ) item "
175 +
" WHERE item.row BETWEEN " + start +
" AND " + end +
" ";
176
177 sql =
string.Format(sql, rowOrder);
178 }
179 strCount =
"select count(0) from (" + strCount +
") item ";
180
181 sql = sql +
";" +
strCount;
182 SqlConnection conn =
new SqlConnection(conString);
183 SqlCommand cmd =
new SqlCommand(sql, conn);
184 //where 替换
185 foreach (
var data
in where)
186 {
187 cmd.Parameters.Add(
new SqlParameter(
"@" +
data.Key, data.Value));
188 }
189 DataSet ds =
new DataSet();
190 SqlDataAdapter adp =
new SqlDataAdapter(cmd);
191 adp.Fill(ds);
192 conn.Close();
193 totalCount = Convert.ToInt32(ds.Tables[
1].Rows[
0][
0]);
194 return ds;
195 }
196 #endregion
197
198 #region 获取数据，不分页
199 /// <summary>
200 /// 获取数据，不分页
201 /// </summary>
202 /// <param name="reportName"></param>
203 /// <param name="where"></param>
204 /// <param name="order"></param>
205 /// <param name="reportType"></param>
206 /// <param name="totalCount"></param>
207 /// <returns></returns>
208 private static DataTable getData(
string reportName, ReportParams p_Params,
string p_ReportXmlName)
209 {
210
211 var order =
p_Params.Order;
212 List<KeyValue>
where =
p_Params.Where;
213 if (
where ==
null)
214 where =
new List<KeyValue>
();
215
216 string reportPath = GetReportPath(p_ReportXmlName);
//报表路径
217
218 if (
string.IsNullOrEmpty(reportPath))
219 return null;
220 string sql = GetSql(reportPath, reportName,
where).Trim();
//获取要查询的SQL语句 及其 参数
221 if (
string.IsNullOrEmpty(sql))
222 return null;
223 string conString = GetConnection();
//获取SQL连接串
224
225 if (
string.IsNullOrEmpty(conString))
226 return null;
227 string rowOrder =
"";
228 if (!
string.IsNullOrEmpty(order))
229 {
230 rowOrder =
"order by " + order +
"";
231 }
232 else
233 {
234 rowOrder =
"order by t.Id desc";
235 }
236
237 var match = Regex.Match(sql,
@"\s+order\s+", RegexOptions.IgnoreCase);
//检查语句中是否含有order by
238 string strCount =
sql;
239 if (match.Success)
//有order by 则舍去order by
240 {
241 strCount = sql.Substring(
0, match.Index);
242 if (
string.IsNullOrEmpty(order))
243 {
244 rowOrder =
sql.Substring(match.Index);
245 }
246
247 //sql = ForSql(strCount, rowOrder); //把order by 加入到rownumber
248 }
249 else
250 {
251 //strCount = sql;
252 sql =
@"select * FROM (" + sql +
" )";
253
254 sql =
string.Format(sql, rowOrder);
255 }
256
257 //strCount = "select count(0) from (" + strCount + ") item ";
258
259 // sql = sql+";" + strCount;
260 SqlConnection conn =
new SqlConnection(conString);
261
262 SqlCommand cmd =
new SqlCommand(sql, conn);
263 //where 替换
264 foreach (
var data
in where)
265 {
266 cmd.Parameters.Add(
new SqlParameter(
"@" +
data.Key, data.Value));
267 }
268 DataSet ds =
new DataSet();
269
270 SqlDataAdapter adp =
new SqlDataAdapter(cmd);
271
272 adp.Fill(ds);
273 conn.Close();
274 //totalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
275 return ds.Tables[
0];
276 }
277
278 #endregion
279
280 #region 导出报表
281 /// <summary>
282 /// 导出报表
283 /// </summary>
284 /// <param name="reportName">报表名称</param>
285 /// <param name="p_ReportXmlName">Xml文件名称</param>
286 /// <param name="where">条件</param>
287 /// <returns></returns>
288 private static DataSet getReportExportData(
string reportName,
string p_ReportXmlName, List<KeyValue>
where)
289 {
290 if (
where ==
null)
291 where =
new List<KeyValue>
();
292
293 string reportPath = GetReportPath(p_ReportXmlName);
//报表路径
294 if (
string.IsNullOrEmpty(reportPath))
295 return null;
296 string reportSql = GetSql(reportPath, reportName,
where);
//获取要查询的SQL语句 及其 参数
297
298 if (
string.IsNullOrEmpty(reportSql))
299 return null;
300
301 string conString = GetConnection();
//获取SQL连接串
302
303 if (
string.IsNullOrEmpty(conString))
304 return null;
305 string sql =
reportSql;
306 SqlConnection conn =
new SqlConnection(conString);
307 SqlCommand cmd =
new SqlCommand(sql, conn);
308 //where 替换
309 foreach (
var data
in where)
310 {
311 cmd.Parameters.Add(
new SqlParameter(
"@" +
data.Key, data.Value));
312 }
313 SqlDataAdapter adapter =
new SqlDataAdapter(cmd);
314 DataSet ds =
new DataSet();
315 adapter.Fill(ds);
316 conn.Close();
317 return ds;
318 }
319 #endregion
320
321 /// <summary>
322 ///
323 /// </summary>
324 /// <param name="sql"></param>
325 /// <param name="order"></param>
326 /// <param name="start"></param>
327 /// <param name="end"></param>
328 /// <returns></returns>
329 private static string ForSql(
string sql,
string order,
int start,
int end)
330 {
331 var match = Regex.Match(sql,
@"[\s+]?SELECT\s+");
332 if (match.Success)
333 {
334 sql = sql.Insert(match.Length,
string.Format(
"Row_Number() OVER ({0}) rowNumber,", order));
335 // sql = string.Format("select Row_Number() OVER ({0}) rowNumber,* from ({1}) t", order, sql);
336 sql =
@" SELECT * FROM (" + sql +
" ) item "
337 +
" WHERE item.rowNumber BETWEEN " + start +
" AND " + end +
" ";
338
339 }
340 return sql;
341 }
342
343 /// <summary>
344 ///
345 /// </summary>
346 /// <param name="sql"></param>
347 /// <param name="order"></param>
348 /// <returns></returns>
349 private static string ForSql(
string sql,
string order)
350 {
351 var match = Regex.Match(sql,
@"[\s+]?SELECT\s+");
352 if (match.Success)
353 {
354 sql = sql.Insert(match.Length,
string.Format(
"Row_Number() OVER ({0}) rowNumber,", order));
355 // sql = string.Format("select Row_Number() OVER ({0}) rowNumber,* from ({1}) t", order, sql);
356 /*sql = @" SELECT * FROM (" + sql + " ) item "
357 + " WHERE item.rowNumber BETWEEN " + start + " AND " + end + " ";*/
358 }
359 return sql;
360 }
361
362 #region 获取连接串
363 /// <summary>
364 /// 获取连接串
365 /// </summary>
366 /// <returns></returns>
367 internal static string GetConnection()
368 {
369 return ConfigurationManager.ConnectionStrings[
"ConnectionString"].ConnectionString;
370 }
371 #endregion
372
373 #region 获取报表路径
374 /// <summary>
375 /// 获取报表路径
376 /// </summary>
377 /// <param name="p_ReportXmlName"></param>
378 /// <returns></returns>
379 internal static string GetReportPath(
string p_ReportXmlName)
380 {
381 string m_BasePath = AppDomain.CurrentDomain.BaseDirectory +
"/bin/Resource/ReportXml/";
382 return m_BasePath + p_ReportXmlName +
".xml";
383 }
384 #endregion
385
386 #region 获取Sql语句
387 /// <summary>
388 /// 获取Sql语句
389 /// </summary>
390 /// <param name="reportPath"></param>
391 /// <param name="reportName"></param>
392 /// <param name="where"></param>
393 /// <returns></returns>
394 internal static string GetSql(
string reportPath,
string reportName, List<KeyValue>
where)
395 {
396 var xDoc = XDocument.Load(reportPath);
//加载XML报表参数
397
398 //读取XML获取参数字段信息
399 var queryXml = (
from q
in xDoc.Descendants(
"report")
400 where q.Attribute(
"id").Value.Trim().ToLower() ==
reportName.Trim().ToLower()
401 select q).AsQueryable();
402
403 var querySql = queryXml.Elements(
"sql").FirstOrDefault();
//查出SQL语句
404 //var m_SqlType = string.Empty;
405 //var querySqlObj = queryXml.Elements("type").FirstOrDefault();
406 //if (m_SqlType != null)
407 //{
408 // m_SqlType = querySqlObj.Value;
409 //}
410
411 #region 语句最后的查询条件
412 if (querySql ==
null)
//如果没配置则返回NULL
413 return null;
414
415 string uperSql =
querySql.Value;
416
417 #region 子查询的查询条件
418 var childAttr = queryXml.Elements(
"childDynamic");
419
420 foreach (
var _child
in childAttr)
421 {
422 int cIndex =
1;
423 var child = _child.Elements(
"isNotEmpty");
424 if (child ==
null)
425 continue;
426 var childEle = _child.Elements(
"isNotEmpty").ToList();
427
428 //StringBuilder para = new StringBuilder();
429 string c_Param = HandleParam(childEle,
where, cIndex);
430 string childEnd = _child.Element(
"childEnd") ==
null ?
"" : _child.Element(
"childEnd").Value;
431 if (c_Param.Length ==
0)
432 {
433 uperSql = uperSql.Replace(_child.Attribute(
"property").Value.ToLower(), childEnd);
434 continue;
435 }
436 uperSql = uperSql.Replace(_child.Attribute(
"property").Value.ToLower(), c_Param.Insert(
0, _child.Attribute(
"prepend").Value +
" ") +
childEnd);
437 }
438
439 #endregion
440
441 StringBuilder sql =
new StringBuilder();
442 var attr = queryXml.Elements(
"dynamic");
//得到设置类型的查询参数 (获取查询条件)
443 if (attr ==
null || attr.Elements(
"isNotEmpty").Count() ==
0)
444 return uperSql;
445 sql.Append(uperSql);
446
447 var paraEle = attr.Elements(
"isNotEmpty").ToList();
448 int index =
1;
449
450 string strPara = HandleParam(paraEle,
where, index);
//where 参数
451
452 #endregion
453 if (!
string.IsNullOrEmpty(strPara))
454 {
455 sql.Append(strPara.Insert(
0, attr.Attributes(
"prepend").Select(q => q.Value).FirstOrDefault() +
" "));
456 }
457 var endSql = queryXml.Elements(
"endSql");
458 if (endSql.FirstOrDefault() !=
null)
459 {
460 sql.Append(endSql.Select(q => q.Value).FirstOrDefault() +
" ");
461 }
462
463 return sql.ToString();
//得到查询的SQL语句,去掉XML里面的多余空格
464
465 }
466 #endregion
467
468 #region 处理所有参数以及Where的值
469 /// <summary>
470 /// 处理所有参数以及Where的值
471 /// </summary>
472 /// <param name="childEle"></param>
473 /// <param name="where"></param>
474 /// <param name="index"></param>
475 /// <returns></returns>
476 private static string HandleParam(List<XElement> childEle, List<KeyValue>
where,
int index)
477 {
478 StringBuilder para =
new StringBuilder();
479 foreach (
var ele
in childEle)
480 {
481 if (ele.Attribute(
"value") !=
null && ele.Attribute(
"value").Value.ToLower() ==
"fixed")
482 {
483 para.Append(
" " + GetStr(index, ele.Value, ele.Attribute(
"prepend").Value));
484 index++
;
485 continue;
486 }
487 var ctn =
where.Where(q => q.Key.ToString().ToLower() == ele.Attribute(
"property").Value.ToLower()).FirstOrDefault();
//判断查询条件是否存在
488 if (ctn ==
null)
489 continue;
490
491 object objType = ele.Attribute(
"type");
492 string type =
string.Empty;
493 if (
null !=
objType)
494 {
495 type = ele.Attribute(
"type").Value.ToLower();
496 }
497
498 //in条件特殊处理
499 if (type ==
"in")
500 {
501 string[] inValue = ctn.Value.ToString().Split(
new char[] {
',' }, StringSplitOptions.RemoveEmptyEntries).Select(n =>
n).ToArray();
502 string strValue =
string.Join(
",", inValue);
503
504 List<
string> p =
new List<
string>
();
505 for (
int i =
0; i < inValue.Count(); i++
)
506 {
507 where.Add(
new KeyValue { Key = ctn.Key.ToString() + i, Value =
inValue[i] });
508 p.Add(
"@" + ctn.Key +
i);
509 }
510
511 if (index ==
1)
512 {
513 para.Append(
" " + ele.Value.ToLower().Replace(
"@" + ctn.Key.ToString().ToLower(),
string.Join(
",", p)));
514 }
515 else
516 {
517 para.Append(
" " + ele.Attribute(
"prepend").Value);
518 para.Append(
" " + ele.Value.ToLower().Replace(
"@" + ctn.Key.ToString().ToLower(),
string.Join(
",", p)));
519 }
520 where.Remove(ctn);
521 index++
;
522 continue;
523 }
524
525 if (type ==
"like" && (ctn.Value.ToString().Contains(
"%") || ctn.Value.ToString().Contains(
"_")))
526 {
527 ctn.Value = ctn.Value.ToString().Replace(
"%",
"[%]").Replace(
"_",
"[_]");
528 para.Append(
" " + GetStr(index, ele.Value, ele.Attribute(
"prepend").Value));
529
530 index++
;
531 continue;
532 }
533
534 para.Append(
" " + GetStr(index, ele.Value, ele.Attribute(
"prepend").Value));
535 index++
;
536 }
537 return para.ToString();
538 }
539 #endregion
540
541 #region type sql的查询条件，index，where 当1的时候紧跟where后面 strIn in的时候值带入SQL语句,XML中的语句,prepend 语句中的关键字（and ,or ）
542 /// <summary>
543 /// type sql的查询条件，index，where 当1的时候紧跟where后面 strIn in的时候值带入SQL语句,XML中的语句,prepend 语句中的关键字（and ,or ）
544 /// </summary>
545 /// <param name="index"></param>
546 /// <param name="eleStr"></param>
547 /// <param name="prepend"></param>
548 /// <returns></returns>
549 private static string GetStr(
int index,
string eleStr,
string prepend)
550 {
551 string para =
string.Empty;
552 if (index ==
1)
553 {
554 return eleStr;
555 }
556 else
557 {
558 return prepend +
" " +
eleStr;
559 }
560 }
561 #endregion
562
563
564 }
565
566 #region 报表参数
567 /// <summary>
568 /// 报表参数
569 /// </summary>
570 public class ReportParams
571 {
572 /// <summary>
573 /// 过滤条件
574 /// </summary>
575 public List<KeyValue> Where {
get;
set; }
576 /// <summary>
577 /// 页面大小
578 /// </summary>
579 public int PageSize {
get;
set; }
580 /// <summary>
581 /// 页面索引
582 /// </summary>
583 public int PageIndex {
get;
set; }
584 /// <summary>
585 /// 排序方式
586 /// </summary>
587 public string Order {
get;
set; }
588 /// <summary>
589 /// 排序字段
590 /// </summary>
591 public string Sort {
get;
set; }
592 }
593
594 public class KeyValue
595 {
596 public object Key {
get;
set; }
597
598 public object Value {
get;
set; }
599 }
600 #endregion
601 }
ReportHelper
转载于:https://www.cnblogs.com/HuberyHu/p/5448746.html
