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); }