long GetCount(CellSetData cs_descr)
{
long result = 0;
if (cs_descr != null)
{
// Сами элементы - по оси 0
// По оси 1 - св-ва элементов
if (cs_descr.Axes.Count > 0)
{
result = cs_descr.Axes[0].Positions.Count;
}
//// Сами элементы - по оси 0
//// По оси 1 - св-ва элементов
//if (cs_descr.Axes.Count == 1 && cs_descr.Axes[0].Positions.Count == 1 && cs_descr.Cells.Count == 1)
//{
// if (cs_descr.Cells[0] != null && cs_descr.Cells[0].Value != null && cs_descr.Cells[0].Value.Value != null)
// {
// try
// {
// result = Convert.ToInt64(cs_descr.Cells[0].Value.Value);
// }
// catch
// {
// }
// }
//}
}
return(result);
}
public CellSetDataProvider(CellSetData cs_descr, DataReorganizationTypes reorganizationType)
{
m_CellSet_Descr = cs_descr;
DataReorganizationType = reorganizationType;
m_Columns = CreateFields(0);
m_Rows = CreateFields(1);
}
public CellSetData RefreshQuery(Func <MdxObject, MdxObject> objectConsumerPattern)
{
CellSetData res = null;
if (!string.IsNullOrEmpty(Query))
{
using (MdxDomProvider provider = MdxDomProvider.CreateProvider())
{
StringBuilder sb = new StringBuilder();
provider.GenerateMdxFromDom(this.CreateWrappedStatement(), sb, new MdxGeneratorOptions());
String new_Query = sb.ToString();
res = ExecuteQuery(new_Query);
//if (!String.IsNullOrEmpty(res))
//{
// Application[QUERY] = new_Query;
//}
}
}
else
{
// Пустой запрос
res = new CellSetData();
}
return(res);
}
public List <MemberDataWrapper> GetChildrenMembers(String cubeName, String subCube, String memberUniqueName)
{
String Set = "{" + String.Format("{0}.Children", memberUniqueName) + "}";
String from_Set = GenerateFromSet(cubeName, subCube);
// Запрос для выполнения
String query = "Select " + Set + " Dimension Properties KEY0, HIERARCHY_UNIQUE_NAME, PARENT_UNIQUE_NAME on 0, {} on 1 from " + from_Set;
CellSetData cs_descr = GetDescription(query);
return(GetMembers(cs_descr));
}
public override String ExportToExcel()
{
String result = String.Empty;
try
{
CellSetData res = RefreshQuery();
PivotDataProvider pivotProvider = new PivotDataProvider(new CellSetDataProvider(res));
result = ExportHelper.ExportToExcel(pivotProvider);
}
catch
{
result = string.Empty;
}
return(result);
}
void OlapDataLoader_DataLoaded(object sender, DataLoaderEventArgs e)
{
bool stopWaiting = true;
try
{
if (e.Error != null)
{
LogManager.LogError(this, e.Error.ToString());
return;
}
if (e.Result.ContentType == InvokeContentType.Error)
{
LogManager.LogError(this, e.Result.Content);
return;
}
MdxQueryArgs mdx_args = e.UserState as MdxQueryArgs;
if (mdx_args != null)
{
CellSetData cs_descr = CellSetData.Deserialize(e.Result.Content);
InitializeTuple(cs_descr);
// Зачитываем метаданные куба в целом
stopWaiting = false;
LogManager.LogInformation(this, this.Name + " - Loading cube metadata.");
MetadataQuery args = CommandHelper.CreateGetCubeMetadataArgs(Connection, CubeName, MetadataQueryType.GetCubeMetadata_AllMembers);
OlapDataLoader.LoadData(args, args);
}
MetadataQuery metadata_args = e.UserState as MetadataQuery;
if (metadata_args != null)
{
CubeDefInfo cs_descr = XmlSerializationUtility.XmlStr2Obj <CubeDefInfo>(e.Result.Content);
m_CopyControl.InitializeMetadata(cs_descr);
}
}
finally
{
if (stopWaiting)
{
IsWaiting = false;
}
}
}
void OnDatesLoaded(CellSetData cs_descr)
{
m_LoadedMembers.Clear();
if (cs_descr.Axes.Count > 0)
{
foreach (PositionData position in cs_descr.Axes[0].Positions)
{
if (position.Members.Count > 0)
{
if (!m_LoadedMembers.ContainsKey(cs_descr.Axes[0].Members[position.Members[0].Id].UniqueName))
{
m_LoadedMembers.Add(cs_descr.Axes[0].Members[position.Members[0].Id].UniqueName, cs_descr.Axes[0].Members[position.Members[0].Id]);
}
}
}
}
}
void InitializeTuple(CellSetData cs_descr)
{
if (cs_descr != null)
{
CellSetDataProvider cellSetProvider = new CellSetDataProvider(cs_descr);
CellInfo cell = cellSetProvider.GetCellInfo(0, 0);
if (cell != null)
{
IDictionary <String, MemberWrap> slice = new Dictionary <String, MemberWrap>();
IDictionary <String, MemberInfo> tuple = cell.GetTuple();
foreach (String hierarchyUniqueName in tuple.Keys)
{
slice.Add(hierarchyUniqueName, new MemberWrap(tuple[hierarchyUniqueName]));
}
m_CopyControl.Initialize(slice);
}
}
}
public List <MemberDataWrapper> GetMembers(String cubeName, String subCube, String set)
{
List <MemberDataWrapper> list = new List <MemberDataWrapper>();
if (String.IsNullOrEmpty(set))
{
return(list);
}
String from_Set = GenerateFromSet(cubeName, subCube);
// Запрос для выполнения
String query = "Select " + set + " Dimension Properties KEY0, HIERARCHY_UNIQUE_NAME, PARENT_UNIQUE_NAME on 0, {} on 1 from " + from_Set;
CellSetData cs_descr = GetDescription(query);
return(GetMembers(cs_descr));
}
public MemberDataWrapper GetMember(String cubeName, String memberUniqueName, List <LevelPropertyInfo> memberProperties)
{
StringBuilder builder = new StringBuilder();
StringBuilder members = new StringBuilder();
members.Append("{");
if (memberProperties != null && memberProperties.Count > 0)
{
// Формируем строку WITH MEMBER
builder.Append("WITH ");
int i = 0;
List <String> used_properties = new List <String>();
foreach (LevelPropertyInfo prop in memberProperties)
{
if (!used_properties.Contains(prop.Name))
{
String memberName = String.Format("[Measures].[-{0}-]", prop.Name);
if (i > 0)
{
members.Append(", ");
}
members.Append(memberName);
builder.AppendFormat("MEMBER {0} AS {1} ", memberName, String.Format("'{0}.Level.Hierarchy.CurrentMember.Properties(\"{1}\", TYPED)'", memberUniqueName, prop.Name));
i++;
used_properties.Add(prop.Name);
}
}
}
members.Append("}");
// Select
builder.AppendFormat("Select {0} on 0, {1} on 1 from {2}", memberUniqueName, members, OlapHelper.ConvertToQueryStyle(cubeName));
CellSetData cs_descr = GetDescription(builder.ToString());
List <MemberDataWrapper> list = GetMembers(cs_descr);
if (list != null && list.Count > 0)
{
return(list[0]);
}
return(null);
}
void Loader_DataLoaded(object sender, DataLoaderEventArgs e)
{
if (e.Error != null)
{
LogManager.LogError(this, e.Error.ToString());
this.m_Clear.Visibility = System.Windows.Visibility.Collapsed;
this.viewer.Content = e.Error.ToString();
this.IsWaiting = false;
return;
}
if (e.Result.ContentType == InvokeContentType.Error)
{
LogManager.LogError(this, e.Result.Content);
this.m_Clear.Visibility = System.Windows.Visibility.Collapsed;
this.viewer.Content = e.Result.Content;
this.IsWaiting = false;
return;
}
try
{
CellSetData cs_descr = CellSetData.Deserialize(e.Result.Content);
OnDatesLoaded(cs_descr);
UpdateGridCells();
this.IsWaiting = false;
}
catch (Exception exc)
{
this.IsWaiting = false;
this.m_Clear.Visibility = System.Windows.Visibility.Collapsed;
LogManager.LogError(this, exc.Message);
throw new Exception(exc.Message);
//this.viewer.Content = exc.Message;
//throw exc;
}
//CellSetData cs_descr = XmlSerializationUtility.XmlStr2Obj<CellSetData>(e.Result);
}
void Loader_DataLoaded(object sender, DataLoaderEventArgs e)
{
IsBusy = false;
if (e.Error != null)
{
LogManager.LogError(this, e.Error.ToString());
return;
}
if (e.Result.ContentType == InvokeContentType.Error)
{
LogManager.LogError(this, e.Result.Content);
return;
}
//CellSetData cs_descr = XmlSerializationUtility.XmlStr2Obj<CellSetData>(e.Result);
CellSetData cs_descr = CellSetData.Deserialize(e.Result.Content);
OnDatesLoaded(cs_descr);
UpdateDates();
}
static CellConditionsDescriptor Deserialize(XmlReader reader)
{
if (reader != null)
{
try
{
CellConditionsDescriptor target = null;
if (!(reader.NodeType == XmlNodeType.Element &&
reader.Name == XML_CellConditionsDescriptor))
{
reader.ReadToFollowing(XML_CellConditionsDescriptor);
}
if (reader.NodeType == XmlNodeType.Element &&
reader.Name == XML_CellConditionsDescriptor)
{
target = new CellConditionsDescriptor();
reader.Read();
var data = Jayrock.Json.Conversion.JsonConvert.Import(reader.Value) as JsonArray;
target.MemberUniqueName = data[0] != null?CellSetData.ConvertFromJson(data[0]).ToString() : String.Empty;
var conditions = data.GetArray(1);
for (int i = 0; i < conditions.Length; i++)
{
var cond_data = conditions.GetArray(i);
CellCondition cond = new CellCondition();
String type = cond_data.GetValue(0) != null?cond_data.GetValue(0).ToString() : CellConditionType.None.ToString();
cond.ConditionType = (CellConditionType)(CellConditionType.Parse(typeof(CellConditionType), type, true));
cond.Value1 = Convert.ToDouble(cond_data.GetValue(1));
cond.Value2 = Convert.ToDouble(cond_data.GetValue(2));
var cellAppearance = cond_data.GetArray(3);
cond.Appearance.BackColor = FromJsonColor(cellAppearance.GetArray(0));
cond.Appearance.BorderColor = FromJsonColor(cellAppearance.GetArray(1));
cond.Appearance.ForeColor = FromJsonColor(cellAppearance.GetArray(2));
cond.Appearance.CustomImageUri = Convert.ToString(cellAppearance.GetValue(3));
var options = cellAppearance.GetArray(4);
cond.Appearance.Options.IgnoreAllOptions = Convert.ToBoolean(options[0]);
cond.Appearance.Options.ShowValue = Convert.ToBoolean(options[1]);
cond.Appearance.Options.UseAllOptions = Convert.ToBoolean(options[2]);
cond.Appearance.Options.UseBackColor = Convert.ToBoolean(options[3]);
cond.Appearance.Options.UseBorderColor = Convert.ToBoolean(options[4]);
cond.Appearance.Options.UseForeColor = Convert.ToBoolean(options[5]);
cond.Appearance.Options.UseImage = Convert.ToBoolean(options[6]);
cond.Appearance.Options.UseProgressBar = Convert.ToBoolean(options[7]);
var progressBarOptions = cellAppearance.GetArray(5);
cond.Appearance.ProgressBarOptions.StartColor = FromJsonColor(progressBarOptions.GetArray(0));
cond.Appearance.ProgressBarOptions.EndColor = FromJsonColor(progressBarOptions.GetArray(1));
cond.Appearance.ProgressBarOptions.MinValue = Convert.ToDouble(progressBarOptions.GetValue(2));
cond.Appearance.ProgressBarOptions.MaxValue = Convert.ToDouble(progressBarOptions.GetValue(3));
cond.Appearance.ProgressBarOptions.IsIndeterminate = Convert.ToBoolean(progressBarOptions.GetValue(4));
target.Conditions.Add(cond);
}
if (reader.NodeType == XmlNodeType.EndElement &&
reader.Name == XML_CellConditionsDescriptor)
{
reader.ReadEndElement();
}
}
return(target);
}
catch (XmlException ex)
{
throw ex;
//return null;
}
}
return(null);
}
public long GetMembersCount(String cubeName, String subCube, String hierarchyUniqueName, int levelIndex)
{
String from_Set = GenerateFromSet(cubeName, subCube);
StringBuilder builder = new StringBuilder();
// В случае, если в выражении FROM используется подкуб то вычисление количества дочерних методом:
// WITH MEMBER [Measures].[-Special_ChildrenCount-]
// AS 'Count(AddCalculatedMembers([Product].[Product Categories].CurrentMember.Children))'
// срабатывает некорректно.
// В данном случае чтобы получить число дочерних с учетом подкуба правильно делать так:
// WITH SET [-SpecialSet-] as [Product].[Product Categories].Members
// MEMBER [Measures].[-Special_ChildrenCount-]
// AS Intersect([-SpecialSet-],[Product].[Product Categories].CurrentMember.Children).Count
String SPECIAL_SET = "[-SpecialSet-]";
String set_expression = String.Empty;
if (UseCalculatedMembers)
{
set_expression = String.Format("AddCalculatedMembers({0}.Members)", hierarchyUniqueName);
}
else
{
set_expression = "{" + String.Format("{0}.Members", hierarchyUniqueName) + "}";
}
String children_expression = String.Empty;
if (UseCalculatedMembers)
{
children_expression = String.Format("AddCalculatedMembers({0}.CurrentMember.Children)", hierarchyUniqueName);
}
else
{
children_expression = "{" + String.Format("{0}.CurrentMember.Children", hierarchyUniqueName) + "}";
}
builder.AppendFormat("WITH SET {0} as {1} ", SPECIAL_SET, set_expression);
String countStr = String.Format("'Intersect({0}, {1}).Count'", SPECIAL_SET, children_expression);
builder.AppendFormat(" MEMBER {0} AS {1} ", CHILDREN_COUNT_MEMBER, countStr);
// СТАРЫЙ ВАРИАНТ - НЕ УЧИТЫВАЕТ ПОДКУБ
//String countStr = String.Format("Count(AddCalculatedMembers({0}.Levels({1}).Members))", hierarchyUniqueName, levelIndex);
//builder.AppendFormat("WITH MEMBER {0} AS '{1}' ", CHILDREN_COUNT_MEMBER, countStr);
String members_expression = String.Empty;
if (UseCalculatedMembers)
{
members_expression = String.Format("AddCalculatedMembers({0}.Levels({1}).Members)", hierarchyUniqueName, levelIndex);
}
else
{
members_expression = "{" + String.Format("{0}.Levels({1}).Members", hierarchyUniqueName, levelIndex) + "}";
}
builder.AppendFormat("Select {0} on 0, {1} on 1 from {2}", members_expression, CHILDREN_COUNT_MEMBER, from_Set);
CellSetData cs_descr = GetDescription(builder.ToString());
long count = GetCount(cs_descr);
return(count);
}
public CellSetDataProvider(CellSetData cs_descr)
: this(cs_descr, DataReorganizationTypes.MergeNeighbors)
{
}
public CellSetDataProvider(CellSetData cs_descr)
{
m_CellSet_Descr = cs_descr;
}
private String ExportToExcel(String schema)
{
InvokeResultDescriptor result = new InvokeResultDescriptor();
String res = String.Empty;
try
{
String sessionId = String.Empty;
String connection = String.Empty;
try
{
MdxQueryArgs args = XmlSerializationUtility.XmlStr2Obj <MdxQueryArgs>(schema);
if (args != null)
{
sessionId = args.SessionId;
connection = args.Connection;
DefaultQueryExecuter queryExecuter = new DefaultQueryExecuter(GetConnection(args.Connection));
if (args.Queries.Count > 0)
{
res = queryExecuter.GetCellSetDescription(args.Queries[0], ref sessionId);
}
}
}
catch (AdomdConnectionException connection_ex)
{
result.Content = connection_ex.Message;
result.ContentType = InvokeContentType.Error;
}
catch (AdomdErrorResponseException response_ex)
{
result.Content = response_ex.Message;
result.ContentType = InvokeContentType.Error;
}
catch (AdomdUnknownResponseException unknown_ex)
{
result.Content = unknown_ex.Message;
result.ContentType = InvokeContentType.Error;
}
catch (InvalidOperationException invalid_ex)
{
result.Content = invalid_ex.Message;
result.ContentType = InvokeContentType.Error;
}
if (!String.IsNullOrEmpty(res))
{
CellSetData cs = CellSetData.Deserialize(res);
PivotDataProvider pivotProvider = new PivotDataProvider(new CellSetDataProvider(cs));
res = ExportHelper.ExportToExcel(pivotProvider);
}
result.Content = res;
if (UseCompress)
{
// Архивация строки
String compesed = ZipCompressor.CompressAndConvertToBase64String(res);
result.Content = compesed;
result.IsArchive = true;
}
result.ContentType = InvokeContentType.MultidimData;
result.Headers.Add(new Header(InvokeResultDescriptor.SESSION_ID, sessionId));
result.Headers.Add(new Header(InvokeResultDescriptor.CONNECTION_ID, connection));
}
catch (Exception)
{
throw;
}
return(InvokeResultDescriptor.Serialize(result));
}
///// <summary>
///// ворзвращает всех предков элементов Set
///// </summary>
///// <param name="set"></param>
///// <param name="cubeName"></param>
///// <param name="hierarchyUniqueName"></param>
///// <param name="ancestorUniqueName"></param>
///// <param name="UseHierarchize"></param>
///// <returns></returns>
//public List<MemberDataWrapper> LoadAscendantsToSet(String set, String cubeName, String hierarchyUniqueName, String ancestorUniqueName)
//{
// if (String.IsNullOrEmpty(set))
// throw new ArgumentNullException("set");
// String tmp = "{" + set + "}";
// //Запрос - который ворзвращает всех предков элементов Set, и сам Set
// string NewSet =
// "Except(" +
// "Generate(" +
// tmp + "," +
// "Ascendants(" + hierarchyUniqueName + ".CurrentMember" + ")" +
// ")" +
// ", " + tmp + ")";
// return LoadSet(NewSet, cubeName, hierarchyUniqueName, ancestorUniqueName, true);
//}
/// <summary>
/// Выполняет запрос к кубу для получения данных указанного Set
/// </summary>
/// <param name="set">Set который нужно получить</param>
/// <param name="useHierarchize">Использовать Hierarchize</param>
/// <returns>CellSetData с результатом запроса</returns>
internal List <MemberDataWrapper> LoadSet(String set, String cubeName, String subCube, String hierarchyUniqueName, bool useHierarchize)
{
if (String.IsNullOrEmpty(set))
{
throw new ArgumentNullException("set");
}
if (String.IsNullOrEmpty(hierarchyUniqueName))
{
throw new ArgumentNullException("hierarchyUniqueName");
}
if (String.IsNullOrEmpty(cubeName))
{
throw new ArgumentNullException("cubeName");
}
// Запрос для выполнения
StringBuilder builder = new StringBuilder();
String from_Set = GenerateFromSet(cubeName, subCube);
String members = "{" + CHILDREN_COUNT_MEMBER;
// В случае, если в выражении FROM используется подкуб то вычисление количества дочерних методом:
// WITH MEMBER [Measures].[-Special_ChildrenCount-]
// AS 'Count(AddCalculatedMembers([Product].[Product Categories].CurrentMember.Children))'
// срабатывает некорректно.
// В данном случае чтобы получить число дочерних с учетом подкуба правильно делать так:
// WITH SET [-SpecialSet-] as [Product].[Product Categories].Members
// MEMBER [Measures].[-Special_ChildrenCount-]
// AS Intersect([-SpecialSet-],[Product].[Product Categories].CurrentMember.Children).Count
String SPECIAL_SET = "[-SpecialSet-]";
String set_expression = String.Empty;
if (UseCalculatedMembers)
{
set_expression = String.Format("AddCalculatedMembers({0}.Members)", hierarchyUniqueName);
}
else
{
set_expression = "{" + String.Format("{0}.Members", hierarchyUniqueName) + "}";
}
String children_expression = String.Empty;
if (UseCalculatedMembers)
{
children_expression = String.Format("AddCalculatedMembers({0}.CurrentMember.Children)", hierarchyUniqueName);
}
else
{
children_expression = "{" + String.Format("{0}.CurrentMember.Children", hierarchyUniqueName) + "}";
}
builder.AppendFormat("WITH SET {0} as {1} ", SPECIAL_SET, set_expression);
String countStr = String.Format("'Intersect({0}, {1}).Count'", SPECIAL_SET, children_expression);
builder.AppendFormat(" MEMBER {0} AS {1} ", CHILDREN_COUNT_MEMBER, countStr);
// СТАРЫЙ ВАРИАНТ - НЕ УЧИТЫВАЕТ ПОДКУБ
//String countStr = String.Format("'Count(AddCalculatedMembers({0}.CurrentMember.Children))' ", hierarchyUniqueName);
//builder.AppendFormat("WITH MEMBER {0} AS {1} ", CHILDREN_COUNT_MEMBER, countStr);
// Список свойств, которые будем получать дополнительно
IList <String> memberProperties = new List <String>();
memberProperties.Add("KEY0");
memberProperties.Add("HIERARCHY_UNIQUE_NAME");
memberProperties.Add("PARENT_UNIQUE_NAME");
foreach (String propName in memberProperties)
{
String memberName = String.Format("[Measures].[-{0}-]", propName);
members = members + "," + memberName;
builder.AppendFormat("MEMBER {0} AS {1} ", memberName,
String.Format("'{0}.CurrentMember.Properties(\"{1}\", TYPED)'", hierarchyUniqueName,
propName));
}
members = members + "}";
// Select
if (useHierarchize)
{
builder.AppendFormat("Select Hierarchize({0}) on 0, {1} on 1 from {2}", set, members, from_Set);
}
else
{
builder.AppendFormat("Select {0} on 0, {1} on 1 from {2}", set, members, from_Set);
}
CellSetData cs_descr = GetDescription(builder.ToString());
return(GetMembers(cs_descr));
}
private List <MemberDataWrapper> GetMembers(CellSetData cs_descr)
{
List <MemberDataWrapper> result = new List <MemberDataWrapper>();
if (cs_descr != null)
{
// Сами элементы - по оси 0
// По оси 1 - св-ва элементов
if (cs_descr.Axes.Count > 0)
{
int colIndex = 0;
foreach (PositionData posColumn in cs_descr.Axes[0].Positions)
{
if (posColumn.Members.Count > 0)
{
MemberDataWrapper wrapper = new MemberDataWrapper(cs_descr.Axes[0].Members[posColumn.Members[0].Id]);
if (cs_descr.Axes.Count > 1)
{
int rowIndex = 0;
foreach (PositionData posRow in cs_descr.Axes[1].Positions)
{
if (posRow.Members.Count > 0)
{
PropertyData prop = new PropertyData();
prop.Name = cs_descr.Axes[1].Members[posRow.Members[0].Id].Caption;
// Название свойства имеет специальный вид -IsDataMember-
// В качестве названия будем использовать подстроку между символами "-"
//String caption = posRow.Members[0].Caption;
//if (caption.StartsWith("-") && caption.EndsWith("-"))
// caption = caption.Trim('-');
// Если такого атрибута нет, то будет исключение
CellData cell_descr = null;
try
{
cell_descr = cs_descr.GetCellDescription(colIndex, rowIndex);
}
catch (AdomdErrorResponseException)
{
}
if (cell_descr != null && cell_descr.Value != CellValueData.Empty && !cell_descr.Value.IsError)
{
prop.Value = cell_descr.Value.Value;
wrapper.Member.MemberProperties.Add(prop);
}
}
rowIndex++;
}
}
result.Add(wrapper);
}
colIndex++;
}
}
}
return(result);
}
