protected void ASPxComboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
ASPxPivotGrid1.BeginUpdate();
switch (SelectedDemo)
{
case 0:
ASPxPivotGrid1.Fields[0].Visible = true;
ASPxPivotGrid1.Fields[0].Caption = "Product Group";
ASPxPivotGrid1.Fields[0].GroupInterval = DevExpress.XtraPivotGrid.PivotGroupInterval.Custom;
ASPxPivotGrid1.Fields[0].AreaIndex = 0;
ASPxPivotGrid1.Fields[4].Caption = "Year";
ASPxPivotGrid1.Fields[4].FilterValues.Clear();
ASPxPivotGrid1.Fields[4].GroupInterval = DevExpress.XtraPivotGrid.PivotGroupInterval.DateYear;
ASPxPivotGrid1.Fields[3].Visible = false;
ASPxPivotGrid1.Fields[3].GroupInterval = PivotGroupInterval.Default;
break;
case 1:
ASPxPivotGrid1.Fields[3].Visible = true;
ASPxPivotGrid1.Fields[3].Caption = "Year - Quarter";
ASPxPivotGrid1.Fields[3].GroupInterval = DevExpress.XtraPivotGrid.PivotGroupInterval.Custom;
ASPxPivotGrid1.Fields[3].AreaIndex = 0;
ASPxPivotGrid1.Fields[4].Caption = "Shipped Date";
ASPxPivotGrid1.Fields[4].FilterValues.Clear();
ASPxPivotGrid1.Fields[4].GroupInterval = DevExpress.XtraPivotGrid.PivotGroupInterval.Default;
ASPxPivotGrid1.Fields[4].AreaIndex = 1;
ASPxPivotGrid1.Fields[0].Visible = false;
ASPxPivotGrid1.Fields[0].GroupInterval = PivotGroupInterval.Default;
break;
}
ASPxPivotGrid1.EndUpdate();
ASPxPivotGrid1.CollapseAll();
}
protected void ASPxPivotGrid1_DataBound(object sender, EventArgs e)
{
if (!IsPostBack)
{
ASPxPivotGrid1.CollapseAll();
}
}
protected void ASPxPivotGrid1_Load(object sender, EventArgs e)
{
if (IsCallback || IsPostBack)
{
return;
}
// Expands the Australia column to be able to retrieve OLAP members
// that correspond to the nested columns.
ASPxPivotGrid1.ExpandValue(true, new object[] { "Australia" });
// Obtains OLAP members corresponding to the Australia and Bendigo values.
IOLAPMember countryMember = ASPxPivotGrid1.GetFieldValueOLAPMember(fieldCountry, 0);
IOLAPMember cityMember = ASPxPivotGrid1.GetFieldValueOLAPMember(fieldCity, 0);
// Exits if the OLAP members were not obtained successfully.
if (countryMember == null || cityMember == null)
{
return;
}
// Specifies a data field whose summary values should be used to sort values
// of the Month field.
fieldMonth.SortBySummaryInfo.Field = fieldSales;
// Specifies a column by which the Month field values should be sorted.
fieldMonth.SortBySummaryInfo.Conditions.Clear();
fieldMonth.SortBySummaryInfo.Conditions.Add(
new PivotGridFieldSortCondition(fieldCountry, "Australia", countryMember.UniqueName));
fieldMonth.SortBySummaryInfo.Conditions.Add(
new PivotGridFieldSortCondition(fieldCity, "Bendigo", cityMember.UniqueName));
}
void InitPivotGrid()
{
string strReportType = "reportproduct";
if (reportType["ReportType"].Equals("ReportMaterial"))
{
strReportType = "reportmaterial";
}
else if (reportType["ReportType"].Equals("ReportDevice"))
{
strReportType = "reportdevice";
}
else if (reportType["ReportType"].Equals("ReportFinish"))
{
strReportType = "reportfinishedproduct";
}
// Change this property to transpose the chart.
ASPxPivotGrid1.OptionsChartDataSource.ProvideDataByColumns = false;
ASPxPivotGrid1.BeginUpdate();
ASPxPivotGrid1.DataSource = GetData(strReportType);
SetFilter();
ASPxPivotGrid1.EndUpdate();
SetSelection();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack && !IsCallback)
{
PivotGridField fieldProductName = ASPxPivotGrid1.Fields["fieldProductName"];
PivotGridField fieldCountry = ASPxPivotGrid1.Fields["fieldCountry"];
ASPxPivotGrid1.BeginUpdate();
try {
fieldProductName.FilterValues.Clear();
fieldProductName.FilterValues.Add("Chai");
fieldProductName.FilterValues.Add("Chang");
fieldProductName.FilterValues.Add("Chartreuse verte");
fieldProductName.FilterValues.Add("Ipoh Coffee");
fieldProductName.FilterValues.Add("Aniseed Syrup");
fieldProductName.FilterValues.Add("Genen Shouyu");
fieldProductName.FilterValues.Add("Gula Malacca");
fieldProductName.FilterValues.Add("Vegie-spread");
fieldCountry.FilterValues.Add("UK");
fieldProductName.FilterValues.FilterType = DevExpress.XtraPivotGrid.PivotFilterType.Included;
fieldCountry.FilterValues.FilterType = DevExpress.XtraPivotGrid.PivotFilterType.Included;
} finally {
ASPxPivotGrid1.EndUpdate();
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
// Specifies the OLAP connection settings.
ASPxPivotGrid1.OLAPDataProvider = OLAPDataProvider.Adomd;
ASPxPivotGrid1.OLAPConnectionString =
@"Provider=MSOLAP;
Data Source=http://demos.devexpress.com/Services/OLAP/msmdpump.dll;
Initial catalog=Adventure Works DW Standard Edition;
Cube name=Adventure Works;
Query Timeout=100;";
// Retrieves fields.
ASPxPivotGrid1.RetrieveFields(PivotArea.ColumnArea, false);
// Adds some fields from the Field List to the specified area to create a report.
ASPxPivotGrid1.Fields["[Customer].[Country].[Country]"].Area = PivotArea.RowArea;
ASPxPivotGrid1.Fields["[Customer].[Country].[Country]"].Visible = true;
ASPxPivotGrid1.Fields["[Customer].[City].[City]"].Area = PivotArea.RowArea;
ASPxPivotGrid1.Fields["[Customer].[City].[City]"].Visible = true;
ASPxPivotGrid1.Fields["[Date].[Fiscal].[Fiscal Year]"].Area = PivotArea.ColumnArea;
ASPxPivotGrid1.Fields["[Date].[Fiscal].[Fiscal Year]"].Visible = true;
ASPxPivotGrid1.Fields["[Measures].[Internet Sales Amount]"].Visible = true;
// Sets the Customization Forms style to Excel2007 with additional capabilities.
ASPxPivotGrid1.OptionsCustomization.CustomizationFormStyle = CustomizationFormStyle.Excel2007;
}
protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack && !IsCallback)
{
ASPxPivotGrid1.DataBind();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
PivotDrillDownDataSource ds = ASPxPivotGrid1.CreateDrillDownDataSource(0, 0);
PropertyDescriptorCollection props = ((ITypedList)ds).GetItemProperties(null);
DataTable table = new DataTable("drilldown");
for (int i = 0; i < props.Count; i++)
{
table.Columns.Add(props[i].Name, props[i].PropertyType);
}
object[] row = new object[props.Count];
for (int i = 0; i < ds.RowCount; i++)
{
for (int j = 0; j < props.Count; j++)
{
row[j] = ds.GetValue(i, j);
}
table.Rows.Add(row);
}
using (StringWriter writer = new StringWriter()) {
table.WriteXml(writer, XmlWriteMode.WriteSchema);
Session["drilldown"] = writer.ToString();
}
}
protected void BindGridView(string columnIndex, string rowIndex)
{
ASPxGridView1.DataSource =
ASPxPivotGrid1.CreateDrillDownDataSource(Int32.Parse(columnIndex),
Int32.Parse(rowIndex));
ASPxGridView1.DataBind();
}
//获取点击单元格的明细数据
protected string getDetail()
{
int rowIndex = int.Parse(Request["rowIndex"]);
int colIndex = int.Parse(Request["colIndex"]);
int pageSize = int.Parse(Request["pageSize"]);
int pageIndex = int.Parse(Request["pageIndex"]);
string sortField = Request["sortField"];
string sortOrder = Request["SortOrder"];
DataTable dt = ASPxPivotGrid1.DataSource as DataTable;
PivotDrillDownDataSource source = ASPxPivotGrid1.CreateDrillDownDataSource(colIndex, rowIndex);
List <int> list = new List <int>();
for (int i = 0; i < source.RowCount; i++)
{
list.Add(source[i].ListSourceRowIndex);
}
var result = dt.Copy();
for (int i = result.Rows.Count - 1; i >= 0; i--)
{
if (list.Contains(i) == false)
{
result.Rows.RemoveAt(i);
}
}
if (!string.IsNullOrEmpty(sortField))
{
if (sortOrder == "asc")
{
result = result.AsEnumerable().OrderBy(c => c[sortField]).CopyToDataTable();
}
else
{
result = result.AsEnumerable().OrderByDescending(c => c[sortField]).CopyToDataTable();
}
}
int total = result.Rows.Count;
for (int i = result.Rows.Count - 1; i >= 0; i--)
{
if (i < pageIndex * pageSize || i >= (pageIndex + 1) * pageSize)
{
result.Rows.RemoveAt(i);
}
}
IsoDateTimeConverter datetype = new IsoDateTimeConverter();
datetype.DateTimeFormat = "yyyy'-'MM'-'dd'T'HH':'mm':'ss";
var objResult = new { total = total, data = result };
return(JsonConvert.SerializeObject(objResult, datetype));
}
protected void Page_Load(object sender, EventArgs e)
{
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath(@"~/App_Data/CustomerReports.xml"));
ASPxPivotGrid1.DataSource = ds.Tables[0];
ASPxPivotGrid1.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
// Creates a new collection of OLAP member properties.
fieldProduct.AutoPopulatedProperties = new string[] { "Color", "List Price" };
ASPxPivotGrid1.DataBind();
// Sets a field's sort mode to Custom to raise the CustomServerModeSort event.
fieldProduct.SortMode = PivotSortMode.Custom;
}
protected void Page_Load(object sender, EventArgs e)
{
if (ASPxRadioButtonList1.SelectedItem != null)
{
mode = Convert.ToString(ASPxRadioButtonList1.SelectedItem.Value);
ASPxPivotGrid1.DataBind();
}
}
protected void ASPxButton2_Click(object sender, EventArgs e)
{
string layout = (string)Session["Layout"];
if (layout == null)
{
return;
}
ASPxPivotGrid1.LoadLayoutFromString(layout);
}
protected void OnASPxGridViewCustomCallback(object sender, ASPxGridViewCustomCallbackEventArgs e)
{
string[] param = e.Parameters.Split('|');
if (param.Length != 3)
{
return;
}
ASPxGridView1.DataSource =
ASPxPivotGrid1.CreateDrillDownDataSource(int.Parse(param[1]), int.Parse(param[2]));
ASPxGridView1.DataBind();
ASPxGridView1.PageIndex = 0;
}
protected void Page_Load(object sender, EventArgs e)
{
string connStr = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=true";
string query = "SELECT TOP 30 e.LastName AS Employee, o.ShipCountry, " +
"o.ShipCity, o.Freight, o.OrderDate FROM Orders o " +
"INNER JOIN Employees e ON o.EmployeeID = e.EmployeeID " +
"ORDER BY Freight DESC";
SqlDataAdapter adapter = new SqlDataAdapter(query, connStr);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
if (!IsPostBack && !IsCallback)
{
// create fields
PivotGridField fieldEmployee = new PivotGridField();
fieldEmployee.ID = "fieldEmployee";
fieldEmployee.FieldName = "Employee";
fieldEmployee.Area = DevExpress.XtraPivotGrid.PivotArea.ColumnArea;
ASPxPivotGrid1.Fields.Add(fieldEmployee);
PivotGridField fieldCountry = new PivotGridField();
fieldCountry.ID = "fieldCountry";
fieldCountry.FieldName = "ShipCountry";
fieldCountry.Area = DevExpress.XtraPivotGrid.PivotArea.RowArea;
ASPxPivotGrid1.Fields.Add(fieldCountry);
PivotGridField fieldCity = new PivotGridField();
fieldCity.ID = "fieldCity";
fieldCity.FieldName = "ShipCity";
fieldCity.Area = DevExpress.XtraPivotGrid.PivotArea.RowArea;
ASPxPivotGrid1.Fields.Add(fieldCity);
PivotGridField fieldFreight = new PivotGridField();
fieldFreight.ID = "fieldFreight";
fieldFreight.FieldName = "Freight";
fieldFreight.Area = DevExpress.XtraPivotGrid.PivotArea.DataArea;
ASPxPivotGrid1.Fields.Add(fieldFreight);
PivotGridField fieldOrderDate = new PivotGridField();
fieldOrderDate.ID = "fieldOrderDate";
fieldOrderDate.FieldName = "OrderDate";
fieldOrderDate.Area = DevExpress.XtraPivotGrid.PivotArea.FilterArea;
fieldOrderDate.ValueFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
fieldOrderDate.ValueFormat.FormatString = "MMM yyyy";
fieldOrderDate.GroupInterval = DevExpress.XtraPivotGrid.PivotGroupInterval.DateMonth;
ASPxPivotGrid1.Fields.Add(fieldOrderDate);
}
ASPxPivotGrid1.DataSource = dataTable;
ASPxPivotGrid1.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
ASPxPivotGrid1.CellTemplate = new CellTemplate();
string columnIndex = Page.Request["ColumnIndex"];
string rowIndex = Page.Request["RowIndex"];
if (!(string.IsNullOrEmpty(columnIndex) || string.IsNullOrEmpty(rowIndex)))
{
ASPxGridView1.DataSource =
ASPxPivotGrid1.CreateDrillDownDataSource(int.Parse(columnIndex), int.Parse(rowIndex));
ASPxGridView1.DataBind();
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
Session["Layout"] = ASPxPivotGrid1.SaveLayoutToString(PivotGridWebOptionsLayout.DefaultLayout);
MemoryStream collapseStateStream = (MemoryStream)(Session["CollapseStateStream"]);
if (collapseStateStream != null)
{
collapseStateStream.Dispose();
}
collapseStateStream = new MemoryStream();
ASPxPivotGrid1.SaveCollapsedStateToStream(collapseStateStream);
Session["CollapseStateStream"] = collapseStateStream;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack && !IsCallback)
{
StringBuilder stb = new StringBuilder();
foreach (PivotGridField field in ASPxPivotGrid1.GetFieldsByArea(DevExpress.XtraPivotGrid.PivotArea.DataArea))
{
stb.Append(field.ID);
stb.Append("|");
}
ASPxPivotGrid1.JSProperties["cpAllowedFields"] = stb.ToString();
}
}
protected void btnLoad_Click(object sender, EventArgs e)
{
MemoryStream collapseStateStream = (MemoryStream)(Session["CollapseStateStream"]);
string layout = (string)(Session["Layout"]);
if (layout == null ||
collapseStateStream == null)
{
return;
}
ASPxPivotGrid1.LoadLayoutFromString(layout, PivotGridWebOptionsLayout.DefaultLayout);
collapseStateStream.Seek(0, SeekOrigin.Begin);
ASPxPivotGrid1.LoadCollapsedStateFromStream(collapseStateStream);
}
private DataTable CreateSummaryTable(ASPxPivotGrid pivotGrid, PivotGridField sourceField, PivotGridField targetField, PivotGridField valueField, object sourceFieldValue)
{
PivotSummaryDataSource ds = ASPxPivotGrid1.CreateSummaryDataSource();
DataTable dt = new DataTable();
dt.Columns.Add("SourceField", typeof(string));
dt.Columns.Add("TargetField", typeof(string));
dt.Columns.Add("Value", typeof(decimal));
for (int i = 0; i < ds.RowCount; i++)
{
if (ds.GetValue(i, sourceField) == sourceFieldValue)
{
dt.Rows.Add(new object[] { ds.GetValue(i, sourceField), ds.GetValue(i, targetField), ds.GetValue(i, valueField) });
}
}
return(dt);
}
protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack)
{
if (Request.Params["__EVENTTARGET"].Contains(ASPxPivotGrid1.ID) && Request.Params["__EVENTARGUMENT"].StartsWith("Data"))
{
string[] args = Request.Params["__EVENTARGUMENT"].Split('|');
if (args.Length == 3)
{
if (!ASPxPivotGrid1.IsDataBound)
{
ASPxPivotGrid1.DataBind();
}
Session["DS"] = Convert(ASPxPivotGrid1.CreateDrillDownDataSource(int.Parse(args[1]), int.Parse(args[2])));
Response.Redirect("DetailPage.aspx");
}
}
}
}
private void PrepareChartData()
{
if (Session["points"] == null)
{
Session["points"] = new List <MySeriesPoint>();
}
((List <MySeriesPoint>)Session["points"]).Clear();
if (hfDataSourceType.Value == "column")
{
for (int i = 0; i < ASPxPivotGrid1.RowCount; i++)
{
string argument = Convert.ToString(ASPxPivotGrid1.GetFieldValue(ASPxPivotGrid1.Fields["MyRow"], i));
if (string.IsNullOrEmpty(argument))
{
argument = "Grand Total";
}
decimal value = Convert.ToDecimal(ASPxPivotGrid1.GetCellValue(this.SelectedColumnIndex, i));
((List <MySeriesPoint>)Session["points"]).Add(new MySeriesPoint(argument, Convert.ToDouble(value)));
}
}
else
{
for (int i = 0; i < ASPxPivotGrid1.ColumnCount; i++)
{
string argument = Convert.ToString(ASPxPivotGrid1.GetFieldValue(ASPxPivotGrid1.Fields["MyDateTime"], i));
if (string.IsNullOrEmpty(argument))
{
argument = "Grand Total";
}
decimal value = Convert.ToDecimal(ASPxPivotGrid1.GetCellValue(i, this.SelectedRowIndex));
((List <MySeriesPoint>)Session["points"]).Add(new MySeriesPoint(argument, Convert.ToDouble(value)));
}
}
}
Dictionary <string, decimal> GetCountrySales()
{
Dictionary <string, decimal> res = new Dictionary <string, decimal>();
bool isColumn = fieldCountry.IsColumn;
int count = isColumn ? ASPxPivotGrid1.ColumnCount : ASPxPivotGrid1.RowCount;
for (int i = 0; i < count; i++)
{
string country = (string)ASPxPivotGrid1.GetFieldValue(fieldCountry, i);
if (string.IsNullOrEmpty(country))
{
continue;
}
decimal value = Convert.ToDecimal(
ASPxPivotGrid1.GetCellValue(isColumn ? i : ASPxPivotGrid1.ColumnCount - 1,
isColumn ? ASPxPivotGrid1.RowCount - 1 : i));
res.Add(country, value);
}
return(res);
}
protected void ASPxPivotGrid1_CustomCallback(object sender, PivotGridCustomCallbackEventArgs e)
{
if (String.IsNullOrEmpty(e.Parameters))
{
return;
}
String[] args = e.Parameters.Split('|');
if (args[0] == "MenuItemClick")
{
switch (args[1])
{
case "Filter":
PivotGridField field = ASPxPivotGrid1.Fields[args[2]];
int itemIndex = Int32.Parse(args[3]);
Object filterValue = ASPxPivotGrid1.GetFieldValueByIndex(field, itemIndex);
field.FilterValues.ValuesIncluded = new Object[] { filterValue };
break;
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack && !IsCallback)
{
PivotGridField field = ASPxPivotGrid1.Fields["Year"];
ASPxPivotGrid1.BeginUpdate();
try
{
field.FilterValues.Clear();
field.FilterValues.Add(1996);
field.FilterValues.FilterType = DevExpress.XtraPivotGrid.PivotFilterType.Included;
}
finally
{
ASPxPivotGrid1.EndUpdate();
}
}
// Sets fields' sort mode to Custom to raise the CustomServerModeSort event.
fieldOrderMonth.SortMode = PivotSortMode.Custom;
fieldCategoryName.SortMode = PivotSortMode.Custom;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack && !IsCallback)
{
PivotGridField field = ASPxPivotGrid1.Fields["Country"];
// Locks the control to prevent excessive updates when multiple properties are modified.
ASPxPivotGrid1.BeginUpdate();
try {
// Clears the filter value collection and add two items to it.
field.FilterValues.Clear();
field.FilterValues.Add("Brazil");
field.FilterValues.Add("USA");
// Specifies that the control should only display the records
// which contain the specified values in the Country field.
field.FilterValues.FilterType = DevExpress.XtraPivotGrid.PivotFilterType.Included;
}
finally {
// Unlocks the control.
ASPxPivotGrid1.EndUpdate();
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
ASPxPivotGrid1.DataSource = ManualDataSet.CreateData().Tables[0];
ASPxPivotGrid1.DataBind();
if (!IsPostBack)
{
Session["selectedRowIndex"] = 0;
Session["selectedColIndex"] = 0;
ASPxPivotGrid1.RetrieveFields();
ASPxPivotGrid1.Fields["MyData"].Area = DevExpress.XtraPivotGrid.PivotArea.DataArea;
ASPxPivotGrid1.Fields["MyRow"].Area = DevExpress.XtraPivotGrid.PivotArea.RowArea;
ASPxPivotGrid1.Fields["MyDateTime"].Area = DevExpress.XtraPivotGrid.PivotArea.ColumnArea;
ASPxPivotGrid1.Fields["MyDateTime"].ValueFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
ASPxPivotGrid1.Fields["MyDateTime"].ValueFormat.FormatString = "d";
((XYDiagram)WebChartControl1.Diagram).AxisX.DateTimeOptions.Format = DateTimeFormat.ShortDate;
}
PrepareChartData();
FillChart();
}
protected void ASPxPivotGrid1_CustomCallback(object sender, DevExpress.Web.ASPxPivotGrid.PivotGridCustomCallbackEventArgs e)
{
int dataFieldIndex = Convert.ToInt32(e.Parameters);
Label1.Text = ASPxPivotGrid1.GetFieldByArea(PivotArea.DataArea, dataFieldIndex).SummaryType.ToString();
}
private void LoadData(List <Dictionary <string, string> > filters)
{
string sql = string.Format("select * from S_UI_Pivot where Code='{0}'", Request["TmplCode"]);
if (isFreePivot)//自由透视表
{
sql = string.Format("select S_UI_FreePivotInstance.*,ConnName from S_UI_FreePivotInstance join S_UI_FreePivot on S_UI_FreePivot.ID=FreePivotID where S_UI_FreePivotInstance.ID='{0}'", Request["ID"]);
}
SQLHelper baseSqlHelper = SQLHelper.CreateSqlHelper("Base");
var dt = baseSqlHelper.ExecuteDataTable(sql);
if (dt.Rows.Count == 0) //找不到定义
{
return;
}
var row = dt.Rows[0];
var sqlHelper = SQLHelper.CreateSqlHelper(row["ConnName"].ToString());
sql = row["SQL"].ToString();
string str = "";
foreach (var item in filters)
{
string value = item.Keys.Contains("FieldValue") ? item["FieldValue"].Replace("'", "") : "";
string queryType = item.Keys.Contains("QueryType") && !string.IsNullOrWhiteSpace(item["QueryType"]) ? item["QueryType"] : "in";
if (!string.IsNullOrEmpty(value))
{
if (queryType == "in")
{
str += string.Format(" and {0} in('{1}')", item["FieldName"], value.Replace(",", "','"));
}
else if (queryType == "like")
{
str += string.Format(" and {0} like '%{1}%'", item["FieldName"], value);
}
else if (queryType.StartsWith("between"))
{
if (!value.Contains(','))
{
value += ",";
}
string v1 = value.Split(',')[0];
string v2 = value.Split(',')[1];
DateTime t1 = new DateTime(1900, 1, 1);
DateTime t2 = new DateTime(9999, 12, 31);
if (DateTime.TryParse(v1, out t1) || DateTime.TryParse(v2, out t2))
{
str += string.Format(" and {0} between '{1}' and '{2} 23:59:59'", item["FieldName"], t1.ToString("yyyy-MM-dd"), t2.ToString("yyyy-MM-dd"));
}
}
else
{
str += string.Format(" and {0} {1} '{2}'", item["FieldName"], item["QueryType"], value);
}
}
}
if (Config.Constant.IsOracleDb)
{
sql = string.Format("select * from ({0}) tb where 1=1 {1}", sql, str);
}
else
{
sql = string.Format("select * from ({0}) as tb where 1=1 {1}", sql, str);
}
DataTable result = sqlHelper.ExecuteDataTable(sql);
enumHtml = "<script type='text/javascript'>";
List <Dictionary <string, string> > fields = new List <Dictionary <string, string> >();
fields.AddRange(userRowItems);
fields.AddRange(userColumnItems);
fields.AddRange(userDataItems);
fields.AddRange(userFilterItems);
foreach (var item in fields)
{
if (!item.ContainsKey("Enum"))
{
continue;
}
if (string.IsNullOrEmpty(item["Enum"]))
{
continue;
}
string strEnum = "";
if (item["Enum"].StartsWith("["))
{
strEnum = item["Enum"];
}
else
{
dt = baseSqlHelper.ExecuteDataTable(
string.Format("select S_M_EnumItem.Code as value,S_M_EnumItem.Name as text from S_M_EnumItem join S_M_EnumDef on S_M_EnumDef.ID=EnumDefID where S_M_EnumDef.Code='{0}'", item["Enum"]));
strEnum = JsonHelper.ToJson(dt);
}
enumHtml += string.Format(@"
var enum_{0} = {1};
", item["FieldName"], strEnum);
if (item["Enum"] == "[]")
{
continue;
}
var enumList = JsonHelper.ToList(strEnum);
result.Columns.Add(item["FieldName"] + "_");
foreach (DataRow row1 in result.Rows)
{
row1[item["FieldName"] + "_"] = GetEnumText(enumList, row1[item["FieldName"]].ToString());
}
result.Columns.Remove(item["FieldName"]);
result.Columns[item["FieldName"] + "_"].ColumnName = item["FieldName"];
}
enumHtml += @"
</script>";
ASPxPivotGrid1.DataSource = result;
ASPxPivotGrid1.DataBind();
}
