private PageOfDaTaSet GetSourceDataSet(Pagination page, AdminCredential admin)
{
using (var db = CreateDao())
{
var sql = new Sql();
sql = sql.Select("[Sql]").From("Sys_view").Where("ID=@0", page.vid);
var sqlStr = db.ExecuteScalar <string>(sql);
sqlStr = AppendListSql(page, admin, db, sqlStr);
PageOfDaTaSet result = db.DataSetPage(page.Page, page.PageSize, new Sql(sqlStr));
return(result);
}
}
private PageOfDaTaSet GetSumDataSet(Pagination page, AdminCredential admin)
{
using (var db = CreateDao())
{
var ds = new PageOfDaTaSet();
var sqlStr = CacheHelper.Single.TryGet($"{page.vid}-{admin.ID}-sumdata-sql", 0, () => { return(""); }).ToString();
if (!string.IsNullOrEmpty(sqlStr))
{
ds = db.DataSet(sqlStr);
}
return(ds);
}
}
/// <summary>
/// 获取列表数据源
/// </summary>
/// <param name="page"></param>
/// <param name="User"></param>
/// <returns></returns>
public string GetListDataSource(string sql, Pagination page, AdminCredential User)
{
PageOfDaTaSet result = GetSourceDataSet(sql, page, User);
if (result.Data.Tables.Count > 0)
{
var sumdataset = GetSumDataSet(page, User);
return(JsonHelper.ToListResultJson(result.Data.Tables[0], sumdataset.Data, result.PageCount, result.PageSize, result.Total));
}
else
{
StringBuilder Json = new StringBuilder();
Json.Append("{\"Data\":[");
Json.Append("],\"PagesCount\": " + 0 + ",\"PageSize\": " + 0 + ",\"Total\": " + 0 + "}");
return(Json.ToString());
}
}
/// <summary>
/// 获取报表显示列
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public string GetReportColumns(int id)
{
DataSet ds = new DataSet();
ItemResult <ReportModel> result_report = new ItemResult <ReportModel>();
//ReportModel item = new ReportModel();
var db = CreateDao();
var sql = new Sql();
sql.Select("*").From("Report").Where("ID=@0", id);
Sys_Report report = base.Get <Sys_Report>(id).Data;
var result = new PageOfDaTaSet();
result = db.DataSet(report.ColumnsSql);
db.CloseSharedConnection();
List <ViewFieldModel> columns = new List <ViewFieldModel>();
foreach (DataColumn column in result.Data.Tables[0].Columns)
{
ViewFieldModel field = new ViewFieldModel();
field.Field = column.ColumnName;
field.Title = column.ColumnName;
if (column.ColumnName.IndexOf("__") != -1)
{
field.Field = column.ColumnName.Substring(0, column.ColumnName.IndexOf("__"));
field.Title = column.ColumnName.Substring(column.ColumnName.IndexOf("__") + 2);
// field.C = column.ColumnName.Substring(column.ColumnName.IndexOf("__") + 2);
}
field.Width = 100;
var length = field.Title.Length;
if (length > 5)
{
field.Width = length * 20 - 20;
}
if (column.ColumnName == "peta_rn")
{
field.Title = "序号";
field.Width = 50;
}
columns.Add(field);
}
return(JsonConvert.SerializeObject(columns));
}
private PageOfDaTaSet GetExportDataDataTable(Pagination page, AdminCredential User)
{
Sys_Report report = base.Get <Sys_Report>(page.vid).Data;
if (page.IsSearch)
{
page.Page = 1;
}
var _sql = report.DataSource;
var db = CreateDao();
if (db.ExecuteScalar <int>("SELECT COUNT(0) FROM Sys_ReportParameters WHERE ReportID=@0", report.ID) > 0 && string.IsNullOrEmpty(page.Filter))
{
return(new PageOfDaTaSet());
}
if (_sql.IndexOf("@auth") != -1)
{
_sql = _sql.Replace("@auth", GetAuthSql(db, User, report.EntityID));
}
List <object> objs = new List <object>();
if (!string.IsNullOrEmpty(page.Filter))
{
List <KendoUIFilter> filters = JsonConvert.DeserializeObject <List <KendoUIFilter> >(page.Filter);
foreach (var filter in filters.OrderBy(e => e.sort).ToList())
{
var value = filter.value;
switch (Convert.ToInt32(filter.type))
{
case (int)ParameterTypeEnum.文本:
value = "%" + filter.value + "%";
break;
case (int)ParameterTypeEnum.数字:
value = filter.value;
break;
case (int)ParameterTypeEnum.日期:
value = value.Replace("00:00:00", "");
if (filter.opera == "<=")
{
value = filter.value + " 23:59:59";
}
break;
case (int)ParameterTypeEnum.时间:
if (filter.opera == "<=")
{
if (filter.value.IndexOf("00:00:00") != -1)
{
value = filter.value.Replace("00:00:00", "");
value = filter.value + " 23:59:59";
}
}
break;
}
objs.Add(value);
}
}
var sql = new Sql(_sql, objs.ToArray());
var result = new PageOfDaTaSet();
if (report.IsPage == 1)
{
result = db.DataSetPage(page.Page, page.PageSize, sql);
}
else
{
result = db.DataSet(_sql, objs.ToArray());
}
ApplicationContext.Log.Info("报表 " + report.Name, "Sql：" + _sql + "/ 参数：" + JsonConvert.SerializeObject(objs));
db.CloseSharedConnection();
return(result);
}
