public virtual object GetQueryData(HttpContext context)
{
YZRequest request = new YZRequest(context);
String datasourceName = request.GetString("DataSource", null);
String query = request.GetString("Query");
QueryParameterCollection queryParams = JArray.Parse(request.GetString("QueryParams")).ToObject <QueryParameterCollection>();
YZClientParamCollection @params = JArray.Parse(Encoding.UTF8.GetString(Convert.FromBase64String(request.GetString("params", YZJsonHelper.Base64EmptyJArray)))).ToObject <YZClientParamCollection>();
bool clientCursor = request.GetBool("clientCursor", false);
//应用查询条件
BPMDBParameterCollection finallyParams = queryParams.CreateNullDBParameters();
foreach (BPMDBParameter @param in finallyParams)
{
YZClientParam clientParam = @params.TryGetItem(@param.Name);
if (clientParam != null && clientParam.value != null)
{
@param.Value = clientParam.value;
}
}
FlowDataTable table = new FlowDataTable();
int rowcount;
using (BPMConnection cn = new BPMConnection())
{
cn.WebOpen();
table.Load(cn, BPMCommandType.Query, query, finallyParams, clientCursor, request.Start, request.Limit, out rowcount);
}
return(new
{
total = rowcount,
children = table.ToDataTable()
});
}
public virtual void ExportGrid2Excel(HttpContext context)
{
YZRequest request = new YZRequest(context);
bool dynamicParams = request.GetBool("dynamicParams", false);
//获得数据 - jsonResponse
string jsonResponse;
JObject jRequest = JObject.Parse(Encoding.UTF8.GetString(Convert.FromBase64String(context.Request.Params["request"])));
string url = (string)jRequest["url"];
url = "~/" + url;
JToken jToken;
using (StringWriter sw = new StringWriter())
{
HtmlTextWriter writer = new HtmlTextWriter(sw);
HttpResponse response = new HttpResponse(writer);
List <string> queryParams = new List <string>();
queryParams.Add("DateFormat=text");
foreach (KeyValuePair <string, JToken> property in (jRequest["params"] as JObject))
{
queryParams.Add(property.Key + "=" + HttpUtility.UrlEncode((string)property.Value, Encoding.Default));
}
HttpRequest callrequest = new HttpRequest(null, context.Request.Url.ToString(), String.Join("&", queryParams.ToArray()));
HttpContext callcontext = new HttpContext(callrequest, response);
IHttpHandler handler = PageParser.GetCompiledPageInstance(url, context.Server.MapPath(url), context);
handler.ProcessRequest(callcontext);
jsonResponse = sw.ToString();
jToken = JToken.Parse(jsonResponse);
if (jToken is JObject)
{
JObject jObject = jToken as JObject;
if (jObject["success"] != null && jObject["success"].Type == JTokenType.Boolean && (bool)jObject["success"] == false)
{
throw new Exception((string)jObject["errorMessage"]);
}
}
}
//将数据转化为Table
DataTable table;
string rootProperty = request.GetString("rootProperty", null);
JArray jTable;
if (String.IsNullOrEmpty(rootProperty))
{
jTable = jToken as JArray;
}
else
{
jTable = (jToken as JObject)[rootProperty] as JArray;
}
foreach (JObject jRow in jTable)
{
foreach (KeyValuePair <string, JToken> jProperty in jRow)
{
if (jProperty.Value is JArray)
{
jRow[jProperty.Key] = Convert.ToString(jProperty.Value);
}
if (jProperty.Value is JObject)
{
jRow[jProperty.Key] = Convert.ToString(jProperty.Value);
}
}
}
table = jTable.ToObject <DataTable>();
table.TableName = "GridStore";
//SQL Server数据库中monery4位小数点处理
foreach (DataRow row in table.Rows)
{
foreach (DataColumn column in table.Columns)
{
object value = row[column];
if (value is decimal)
{
value = (decimal)Decimal.ToDouble((decimal)value);
}
row[column] = value;
}
}
//生成Excel
string templateExcel = request.GetString("templateExcel", String.Empty);
HSSFWorkbook book = null;
if (String.IsNullOrEmpty(templateExcel))
{
book = this.NoTemplateExport(context, table);
}
else
{
if (!templateExcel.StartsWith("~/"))
{
templateExcel = "~/" + templateExcel;
}
Dictionary <string, string> reportParams = new Dictionary <string, string>();
//获得查询参数
foreach (KeyValuePair <string, JToken> property in (jRequest["params"] as JObject))
{
reportParams.Add(property.Key, (string)property.Value);
}
if (!reportParams.ContainsKey("Kwd"))
{
reportParams.Add("Kwd", "");
}
if (dynamicParams)
{
string strRuntimeParams = reportParams["params"];
if (!String.IsNullOrEmpty(strRuntimeParams))
{
YZClientParamCollection runtimeParams = JArray.Parse(Encoding.UTF8.GetString(Convert.FromBase64String(strRuntimeParams))).ToObject <YZClientParamCollection>();
foreach (YZClientParam clientParams in runtimeParams)
{
reportParams[clientParams.name] = Convert.ToString(clientParams.value);
}
}
}
//打开文件
using (FileStream file = new FileStream(context.Server.MapPath(templateExcel), FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
book = new HSSFWorkbook(file);
}
DataSet dataset = new DataSet();
dataset.Tables.Add(table);
YZExcelGenerate.Fill(book, reportParams, dataset);
YZExcelGenerate.PrepareForOutput(book);
}
//Excel文件保存到流
byte[] bytes;
using (MemoryStream ms = new MemoryStream())
{
book.Write(ms);
bytes = ms.ToArray();
}
//导出文件名
string fileName = context.Request.Params["fileName"];
if (String.IsNullOrEmpty(fileName))
{
fileName = "Export";
}
fileName += YZStringHelper.DateToString(DateTime.Now) + ".xls";
this.ProcessResponseHeader(context, fileName, true);
context.Response.BinaryWrite(bytes);
//this.OnExported(context, table);
//设置Response头
//context.Response.Clear();
//context.Response.ContentType = "application/vnd.ms-excel";
//context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
//context.Response.AppendHeader("Content-Length", bytes.Length.ToString());
//context.Response.BinaryWrite(bytes);
//context.Response.End();
}
protected virtual void ApplyExcelData(YZRequest request, JObject rv, ReportExcelView view, DataTable dataTable, YZClientParamCollection queryParams)
{
string excelTemplate = request.Context.Server.MapPath("~/YZSoft/Report/Templates/" + view.TemplateFile);
Dictionary <string, string> reportParams = new Dictionary <string, string>();
reportParams.Add("ReportDate", YZStringHelper.DateToStringL(DateTime.Today));
foreach (YZClientParam queryParam in queryParams)
{
reportParams.Add(queryParam.name, Convert.ToString(queryParam.value));
}
dataTable.TableName = "GridStore";
//打开文件
HSSFWorkbook book;
using (FileStream file = new FileStream(excelTemplate, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
book = new HSSFWorkbook(file);
}
//填充数据
DataSet dataset = new DataSet();
dataset.Tables.Add(dataTable);
YZExcelGenerate.Fill(book, reportParams, dataset);
YZExcelGenerate.PrepareForOutput(book);
//调试输出
//using (FileStream fs = new FileStream(@"e:\abc.xls", FileMode.Create))
//{
// book.Write(fs);
// fs.Close();
//}
book.GetSheetAt(0).DisplayGridlines = false;
using (MemoryStream stream = new MemoryStream())
{
book.Write(stream);
string htmlFile = FileConvert.Excel2Html(stream, Path.GetExtension(excelTemplate));
rv["htmlFile"] = Path.GetFileName(htmlFile);
}
}
public virtual JObject GetReportData(HttpContext context)
{
YZRequest request = new YZRequest(context);
string path = request.GetString("path");
string viewName = request.GetString("viewName", null);
string outputType = request.GetString("outputType", "");
YZClientParamCollection runtimeParams = JArray.Parse(Encoding.UTF8.GetString(Convert.FromBase64String(request.GetString("params", YZJsonHelper.Base64EmptyJArray)))).ToObject <YZClientParamCollection>();
//获得数据
Report report;
ReportView view;
DataTable dataTable;
int rowcount;
using (BPMConnection cn = new BPMConnection())
{
cn.WebOpen();
//获得报表定义
report = Report.Open(cn, path);
if (String.IsNullOrEmpty(viewName))
{
view = report.DefaultView;
}
else
{
view = report.Views.TryGetItem(viewName);
}
string srcdata = request.GetString("srcdata", null);
if (String.IsNullOrEmpty(srcdata))
{
BPMDBParameterCollection selectParameters = report.QueryParameters.CreateNullDBParameters();
//应用查询条件
foreach (BPMDBParameter selectParam in selectParameters)
{
YZClientParam clientParam = runtimeParams.TryGetItem(selectParam.Name);
if (clientParam != null && clientParam.value != null)
{
selectParam.Value = clientParam.value;
}
}
cn.RequestParams["sortstring"] = request.GetSortString("");
cn.UpdateRequestParams();
//获得数据
FlowDataTable ftable = new FlowDataTable();
ftable.Load(cn, BPMCommandType.Report, path, selectParameters, report.ClientCursor, request.Start, request.Limit, out rowcount);
dataTable = ftable.ToDataTable();
}
else
{
dataTable = JArray.Parse(Encoding.UTF8.GetString(Convert.FromBase64String(srcdata))).ToObject <DataTable>();
rowcount = request.GetInt32("total");
}
}
//将数据转化为Json集合
JObject rv = new JObject();
rv[YZJsonProperty.total] = rowcount;
rv["srcdata"] = Convert.ToBase64String(Encoding.UTF8.GetBytes(JArray.FromObject(dataTable).ToString(Formatting.None, YZJsonHelper.Converters)));
JArray children = new JArray();
rv.Add("children", children);
//不管是什么view都要给数据
foreach (DataRow row in dataTable.Rows)
{
JObject item = new JObject();
children.Add(item);
foreach (DataColumn column in dataTable.Columns)
{
object value = row[column.ColumnName];
item[column.ColumnName] = JToken.FromObject(value);
if (NameCompare.EquName(column.ColumnName, "TaskID") && (value is int))
{
item["Token"] = YZSecurityHelper.GenTaskAccessToken((int)value);
}
//为任务链接生成Token
ReportColumnInfo colInfo = report.ReportColumnInfos.TryGetItem(column.ColumnName);
if (colInfo != null && colInfo.LinkType == ReportLinkType.Task)
{
ParameterFill paramFill = colInfo.ParametersFill.TryGetItem("@TaskID");
if (paramFill != null)
{
int linktoTaskID;
if (Int32.TryParse(Convert.ToString(row[paramFill.FillWith]), out linktoTaskID))
{
item[column.ColumnName + "Token"] = YZSecurityHelper.GenTaskAccessToken((int)linktoTaskID);
}
}
}
}
}
if (String.Compare(outputType, "Export", true) != 0)
{
if (view is ReportMSChartView)
{
this.ApplyMSChartData(request, rv, view as ReportMSChartView, dataTable);
}
if (view is ReportExcelView)
{
this.ApplyExcelData(request, rv, view as ReportExcelView, dataTable, runtimeParams);
}
}
return(rv);
}
