using Izenda.BI.Core;
							using Izenda.BI.Core.QueryEngine;
							using Izenda.BI.Framework.Components.QueryExpressionTree;
							using Izenda.BI.Framework.Constants;
							using Izenda.BI.Framework.CustomConfiguration;
							using Izenda.BI.Framework.Models;
							using Izenda.BI.Framework.Models.Common;
							using Izenda.BI.Framework.Models.ReportDesigner;
							using Izenda.BI.Framework.Utility;
							using System;
							using System.Collections.Generic;
							using System.Linq;
							using System.Web;
							using System.Composition.AttributedModel.dll;
							using Izenda.BI.Framework.Components.QueryExpressionTree.Operator;
							using Operator = Izenda.BI.Framework.Enums.DateTimeOperator;
							namespace IAdHocExtensionSample
							{
							[Export(typeof(IAdHocExtension))]
							public class AdHocExtensionSample : DefaultAdHocExtension
							{
							/// <summary>
							/// Sample Requirement:
							/// If logged user has role Manager --> look up "South America" => "SA", "North America" => "NA"
							/// If logged user has role Employee --> look up "Europe" => "EU"
							/// </summary>
							/// <param name="filterField"></param>
							/// <param name="data"></param>
							/// <returns></returns>
							public override List<string> OnPostLoadFilterData(ReportFilterField filterField, List<string> data)
							{
							// override dropdown value based on user role for filter on view "OrderDetailsByRegion" and field "CountryRegionName"
							if (filterField.SourceDataObjectName == "OrderDetailsByRegion" && filterField.SourceFieldName == "CountryRegionName"
							&& (HttpContext.Current.User.IsInRole("Manager") || HttpContext.Current.User.IsInRole("Employee")))
							{
							// override dropdown's value based on User role
							//Manager, look up "South America" => "SA", "North America" => "NA"
							if (HttpContext.Current.User.IsInRole("Manager"))
							{
							var indexSA = data.IndexOf("South America");
							if (indexSA != -1)
							data[indexSA] = "SA";
							var indexNA = data.IndexOf("North America");
							if (indexNA != -1)
							data[indexNA] = "NA";
							}
							// Employee, look up "Europe" => "EU"
							if (HttpContext.Current.User.IsInRole("Employee"))
							{
							var indexEU = data.IndexOf("Europe");
							if (indexEU != -1)
							data[indexEU] = "EU";
							}
							}
							return base.OnPostLoadFilterData(filterField, data);
							}
							/// <summary>
							/// Sample Requirement:
							/// If logged user has role Manager --> show some regions ("South America", "North America")
							/// If logged user has role Employee --> show only one region ("Europe")
							/// </summary>
							/// <param name="fieldInfo"></param>
							/// <returns></returns>
							public override List<ValueTreeNode> OnLoadFilterDataTree(QuerySourceFieldInfo fieldInfo)
							{
							var result = new List<ValueTreeNode>();
							if (fieldInfo.QuerySourceName == "OrderDetailsByRegion" && fieldInfo.Name == "CountryRegionName"
							&& (HttpContext.Current.User.IsInRole("Manager") || HttpContext.Current.User.IsInRole("Employee")))
							{
							//Node [All] is required for UI to render.
							var rootNode = new ValueTreeNode { Text = "[All]", Value = "[All]" };
							rootNode.Nodes = new List<ValueTreeNode>();
							if (HttpContext.Current.User.IsInRole("Manager"))
							{
							rootNode.Nodes.Add(new ValueTreeNode { Text = "South America", Value = "South America" });
							rootNode.Nodes.Add(new ValueTreeNode { Text = "North America", Value = "North America" });
							}
							if (HttpContext.Current.User.IsInRole("Employee"))
							{
							rootNode.Nodes.Add(new ValueTreeNode { Text = "Europe", Value = "Europe" });
							}
							result.Add(rootNode);
							}
							return result;
							}
							/// <summary>
							/// Sample Requirement:
							/// If logged user has role Manager --> show some regions ("WA", "[Blank]")
							/// If logged user has role Employee --> show only one region ("Europe")
							/// </summary>
							/// <param name="fieldInfo"></param>
							/// <returns></returns>
							public override ReportFilterSetting SetHiddenFilters(SetHiddenFilterParam param)
							{
							var filterFieldName = "ShipRegion";
							Func<ReportFilterSetting, int, QuerySource, QuerySourceField, Guid, Relationship, int> addHiddenFilters = (result, filterPosition, querySource, field, equalOperator, rel) =>
							{
							var firstFilter = new ReportFilterField
							{
							Alias = $"ShipRegion{filterPosition}",
							QuerySourceId = querySource.Id,
							SourceDataObjectName = querySource.Name,
							QuerySourceType = querySource.Type,
							QuerySourceFieldId = field.Id,
							SourceFieldName = field.Name,
							DataType = field.DataType,
							Position = ++filterPosition,
							OperatorId = equalOperator,
							Value = "WA",
							RelationshipId = rel?.Id,
							IsParameter = false,
							ReportFieldAlias = null
							};
							var secondFilter = new ReportFilterField
							{
							Alias = $"ShipRegion{filterPosition}",
							QuerySourceId = querySource.Id,
							SourceDataObjectName = querySource.Name,
							QuerySourceType = querySource.Type,
							QuerySourceFieldId = field.Id,
							SourceFieldName = field.Name,
							DataType = field.DataType,
							Position = ++filterPosition,
							OperatorId = equalOperator,
							Value = "[Blank]",
							RelationshipId = rel?.Id,
							IsParameter = false,
							ReportFieldAlias = null
							};
							result.FilterFields.Add(firstFilter);
							result.FilterFields.Add(secondFilter);
							var logic = $"({filterPosition - 1} OR {filterPosition})";
							if (string.IsNullOrEmpty(result.Logic))
							{
							result.Logic = logic;
							}
							return filterPosition;
							};
							var filterSetting = new ReportFilterSetting()
							{
							FilterFields = new List<ReportFilterField>()
							};
							var position = 0;
							var ds = param.ReportDefinition.ReportDataSource;
							// Build the hidden filters for CountryRegionName
							foreach (var querySource in param.QuerySources // Scan thru the query sources that are involved in the report
							.Where(x => x.QuerySourceFields.Any(y => y.Name.Equals(filterFieldName, StringComparison.OrdinalIgnoreCase)))) // Take only query sources that have filter field name
							{
							// Pick the relationships that joins the query source as primary source
							// Setting the join ensure the proper table is assigned when using join alias in the UI
							var rels = param.ReportDefinition.ReportRelationship.
							Where(x => x.JoinQuerySourceId == querySource.Id)
							.ToList();
							// Find actual filter field in query source
							var field = querySource.QuerySourceFields.FirstOrDefault(x => x.Name.Equals(filterFieldName, StringComparison.OrdinalIgnoreCase));
							// Pick the equal operator
							var equalOperator = Izenda.BI.Framework.Enums.FilterOperator.FilterOperator.EqualsManualEntry.GetUid();
							// In case there is no relationship that the query source is joined as primary
							if (rels.Count() == 0)
							{
							// Just add hidden filter with null relationship
							position = addHiddenFilters(filterSetting, position, querySource, field, equalOperator, null);
							}
							else
							{
							// Loop thru all relationships that the query source is joined as primary and add the hidden field associated with each relationship
							foreach (var rel in rels)
							{
							position = addHiddenFilters(filterSetting, position, querySource, field, equalOperator, rel);
							}
							}
							}
							return filterSetting;
							}
							/// <summary>
							/// Sample Requirement:
							/// Remove all Map report parts before running
							/// </summary>
							/// <param name="fieldInfo"></param>
							/// <returns></returns>
							public override ReportDefinition OnPreExecute(ReportDefinition report)
							{
							if (report.ReportPart.Any(x => x.ReportPartContent.Type == ReportPartContentType.Map))
							{
							var filteredReportPart = report.ReportPart.Where(x => x.ReportPartContent.Type != ReportPartContentType.Map).ToList();
							report.ReportPart = filteredReportPart;
							}
							return report;
							}
							/// <summary>
							/// Sample Requirement:
							/// Limit the execution result to the first 1000 rows only (although the database may return more than that)
							/// </summary>
							/// <param name="fieldInfo"></param>
							/// <returns></returns>
							public override List<IDictionary<string, object>> OnPostExecute(QueryTree executedQueryTree, List<IDictionary<string, object>> result)
							{
							return result.Take(1000).ToList();
							}
							/// <summary>
							/// Sample Requirement:
							/// Log the queries without result limit operator
							/// </summary>
							/// <param name="fieldInfo"></param>
							/// <returns></returns>
							public override QueryTree OnExecuting(QueryTree queryTree)
							{
							var nodeVisitor = new QueryTreePathAnalyzeVisitor(new ExtensibilityFactory(), queryTree.ContextData);
							nodeVisitor.ContextData = queryTree.ContextData;
							queryTree.Root.Accept(nodeVisitor);
							var resultLimitOperator = new ResultLimitOperator()
							{
							ChildOperand = new Operand()
							{
							QuerySource = new QuerySource()
							}
							};
							try
							{
							nodeVisitor.Visit(resultLimitOperator);
							}
							catch (Exception)
							{
							Console.WriteLine("LOG: Query with no limit");
							}
							return queryTree;
							}
							/// <summary>
							/// Sample Requirement:
							/// If report filter includes only OrderDetailsByRegion.CountryRegionName, return pre-defined list and skip querying the database
							/// If not, let system query the database
							/// </summary>
							/// <param name="fieldInfo"></param>
							/// <returns></returns>
							public override List<string> OnPreLoadFilterData(ReportFilterSetting filterSetting, out bool handled)
							{
							handled = false;
							List<String> result = null;
							if (filterSetting.FilterFields.Count == 1
							&& filterSetting.FilterFields.Any(
							x => x.SourceDataObjectName.Equals("OrdersByRegion")
							&& x.SourceFieldName.Equals("CountryRegionName")))
							{
							handled = true;
							result = new List<string>()
							{
							"Europe",
							"North America",
							"South America"
							};
							}
							return result;
							}
							/// <summary>
							/// Sample Requirement:
							/// Returns a custom list of 6 In Time Periods
							/// </summary>
							public override List<CustomTimePeriod> LoadCustomTimePeriod()
							{
							var result = new List<CustomTimePeriod>
							{
							new CustomTimePeriod("Tomorrow",
							DateTime.Now, DateTime.Now.AddDays(1), Operator.BetweenDateTime),
							new CustomTimePeriod("Previous Date -> DateTime Now",
							() => DateTime.Now.AddDays(-1), () => DateTime.Now, Operator.BetweenDateTime),
							new CustomTimePeriod("Less Than 2 Days Old",
							2, Operator.LessThanDaysOld),
							new CustomTimePeriod("Greater Than 2 Days Old",
							() => 2, Operator.GreaterThanDaysOld),
							new CustomTimePeriod(">= Date Time Now + 2 Days",
							DateTime.Now.AddDays(2), Operator.GreaterThanOrEqualsCalender),
							new CustomTimePeriod("<= Date Time Now - 2 Days",
							() => DateTime.Now.AddDays(-2), Operator.LessThanOrEqualsCalendar)
							};
							// using Operator = Izenda.BI.Framework.Enums.DateTimeOperator;
							return result;
							}
							/// <summary>
							/// Sample Requirement:
							/// Returns a custom list of 3 data formats
							/// </summary>
							public override List<DataFormat> LoadCustomDataFormat()
							{
							var result = new List<DataFormat>
							{
							new DataFormat
							{
							Name = "0,000",
							DataType = DataType.Numeric,
							Category = IzendaKey.CustomFormat,
							FormatFunc = (x) =>
							{
							return ((int)x).ToString("0,000");
							}
							},
							new DataFormat
							{
							Name = "$0,000",
							DataType = DataType.Numeric,
							Category = IzendaKey.CustomFormat,
							FormatFunc = (x) =>
							{
							return ((int)x).ToString("$0,000");
							}
							},
							new DataFormat
							{
							Name = "$0,000",
							DataType = DataType.Numeric,
							Category = IzendaKey.CustomFormat,
							FormatFunc = (x) =>
							{
							return ((decimal)x).ToString("C0");
							}
							}
							};
							return result;
							}
							}
							}