public static List <T> GetReportData <T>(IDbConnection conn, string reportName, int pageSize, int pageIndex, string order, IDictionary <string, object> where, bool isPage, out int totalCount)
{
try
{
XElement root = XmlConfigManager.GetRepsConfig();
XElement rep = root.Elements("Rep").Where(q => q.Attribute("key").Value == reportName).FirstOrDefault();
if (rep == null)
{
throw new Exception("xml没有相关配置");
}
string reportsql = rep.Element("ReportSql") == null ? "" : rep.Element("ReportSql").Value;
string countSql = rep.Element("TotalSql") == null ? "" : rep.Element("TotalSql").Value;
IDictionary <string, object> dataRes = new Dictionary <string, object>();
Dictionary <string, object> data = new Dictionary <string, object>();
foreach (var itemEle in rep.Elements("Dynamic"))
{
List <ReportWhereEntity> list = FillReportWhereEntity(itemEle.ToString());
string dynamicStr = GetDynamicStr(list, where, ref dataRes);
string dyProperty = itemEle.Attribute("property").Value;
reportsql = reportsql.Replace(dyProperty, dynamicStr);
if (isPage)
{
countSql = countSql.Replace(dyProperty, dynamicStr);
}
if (data != null)
{
foreach (var item in dataRes)
{
if (data.Keys.Contains(item.Key))
{
continue;
}
data.Add(item.Key, item.Value);
}
}
}
if (isPage)
{
if (string.IsNullOrEmpty(order))
{
throw new Exception("order must not be null!");
}
int start = (pageIndex - 1) * pageSize;
reportsql = reportsql + @" order by {0} OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY";
reportsql = string.Format(reportsql, order, start, pageSize);
}
if (where != null)
{
foreach (var item in where)
{
if (!data.Keys.Contains(item.Key.ToLower()))
{
data.Add(item.Key, item.Value);
}
}
}
DynamicParameters args = new DynamicParameters();
List <T> result = new List <T>();
foreach (var item in data)
{
args.Add(item.Key, item.Value);
}
result = SqlMapper.Query <T>(conn, reportsql, args).ToList();
if (isPage)
{
List <SqlParameter> parCountList = new List <SqlParameter>();
foreach (var item in data)
{
parCountList.Add(new SqlParameter(item.Key, item.Value));
}
totalCount = SqlMapper.ExecuteScalar <int>(conn, countSql, args);
}
else
{
totalCount = 0;
}
return(result);
}
catch (Exception e)
{
totalCount = 0;
// log
//Log.Default.Error(e);
return(null);
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
}
}
public static int ExecReport(IDbConnection conn, string reportName, IDictionary <string, object> where)
{
XElement root = XmlConfigManager.GetRepsConfig();
XElement rep = root.Elements("Rep").Where(q => q.Attribute("key").Value == reportName).FirstOrDefault();
if (rep == null)
{
throw new Exception("xml没有相关配置");
}
string reportsql = rep.Element("ReportSql") == null ? "" : rep.Element("ReportSql").Value;
IDictionary <string, object> dataRes = new Dictionary <string, object>();
Dictionary <string, object> data = new Dictionary <string, object>();
foreach (var itemEle in rep.Elements("Dynamic"))
{
List <ReportWhereEntity> list = FillReportWhereEntity(itemEle.ToString());
string dynamicStr = GetDynamicStr(list, where, ref dataRes);
string dyProperty = itemEle.Attribute("property").Value;
reportsql = reportsql.Replace(dyProperty, dynamicStr);
if (data != null)
{
foreach (var item in dataRes)
{
if (data.Keys.Contains(item.Key))
{
continue;
}
data.Add(item.Key, item.Value);
}
}
}
if (where != null)
{
foreach (var item in where)
{
if (!data.Keys.Contains(item.Key.ToLower()))
{
data.Add(item.Key, item.Value);
}
}
}
int result = 0;
DynamicParameters args = new DynamicParameters();
foreach (var item in data)
{
args.Add(item.Key, item.Value);
}
result = SqlMapper.Execute(conn, reportsql, args);
if (conn.State != ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
return(result);
}
