TSQL Essbase Source Code


using System;
using System.IO;
using System.Net;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Net.Cache;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
public static ArrayList XmlaResultCollectionGlobal = new ArrayList();
private class XmlaResult
{
// public SqlString XmlaRowValue;
public SqlDecimal DblValue;
public SqlString Dim01;
public SqlString Dim02;
public SqlString Dim03;
public SqlString Dim04;
public SqlString Dim05;
public SqlString Dim06;
public SqlString Dim07;
public SqlString Dim08;
public SqlString Dim09;
public SqlString Dim10;
public SqlString Dim11;
public SqlString Dim12;
public SqlString Dim13;
public SqlString Dim14;
public SqlString Dim15;
public SqlString Dim16;
public SqlString Dim17;
public SqlString Dim18;
public SqlString Dim19;
public SqlString Dim20;
public SqlString Dim21;
public SqlString Dim22;
public SqlString Dim23;
public SqlString Dim24;
public SqlString Dim25;
public SqlString Dim26;
public SqlString Dim27;
public SqlString Dim28;
public SqlString Dim29;
public SqlString Dim30;
// public SqlString StrValue;
public XmlaResult(String xmlaRowValue, String vTypeQyery)
{
try
{
/* XmlaRowValue = xmlaRowValue;//.Substring(0,3999);*/

string[] DataSet = xmlaRowValue.Split(new Char[] { ‘|’ });
string strPrevValue;
Array.Reverse(DataSet);
DblValue = Decimal.Parse(DataSet[1]);
// StrValue = DataSet[1];
int ii = 31;
int jj = -1;
bool isProcess;
strPrevValue = “”;
int modI;

foreach (String curDataSet in DataSet)
{
if (curDataSet.Length > 1)
{
jj = jj + 1;
isProcess = false;

if ((!vTypeQyery.Contains(“mdxLev0”)))
{
isProcess = true;
}
if (!isProcess)
{
modI = ii % 2;

if (strPrevValue.Contains(“0”) && strPrevValue.Length == 1)
{
isProcess = true;
}
}
if (isProcess)
{
ii = ii – 1;
if (30 == ii) Dim30 = curDataSet;
if (29 == ii) Dim29 = curDataSet;
if (28 == ii) Dim28 = curDataSet;
if (27 == ii) Dim27 = curDataSet;
if (26 == ii) Dim26 = curDataSet;
if (25 == ii) Dim25 = curDataSet;
if (24 == ii) Dim24 = curDataSet;
if (23 == ii) Dim23 = curDataSet;
if (22 == ii) Dim22 = curDataSet;
if (21 == ii) Dim21 = curDataSet;
if (20 == ii) Dim20 = curDataSet;
if (19 == ii) Dim19 = curDataSet;
if (18 == ii) Dim18 = curDataSet;
if (17 == ii) Dim17 = curDataSet;
if (16 == ii) Dim16 = curDataSet;
if (15 == ii) Dim15 = curDataSet;
if (14 == ii) Dim14 = curDataSet;
if (13 == ii) Dim13 = curDataSet;
if (12 == ii) Dim12 = curDataSet;
if (11 == ii) Dim11 = curDataSet;
if (10 == ii) Dim10 = curDataSet;
if ( 9 == ii) Dim09 = curDataSet;
if ( 8 == ii) Dim08 = curDataSet;
if ( 7 == ii) Dim07 = curDataSet;
if ( 6 == ii) Dim06 = curDataSet;
if ( 5 == ii) Dim05 = curDataSet;
if ( 4 == ii) Dim04 = curDataSet;
if ( 3 == ii) Dim03 = curDataSet;
if ( 2 == ii) Dim02 = curDataSet;
if ( 1 == ii) Dim01 = curDataSet;
}
}
strPrevValue = curDataSet;
}
}
catch (Exception e)
{
throw new ArgumentException(“\n \n Error in CLR Function:\n”, e.Message + “\n \n”);
}

}
}
public static string getNextLocalIP()
{
Random rnd0 = new Random();
Random rnd1 = new Random();
Random rnd2 = new Random();
Random rnd3 = new Random();
Random rnd4 = new Random();
Random rnd5 = new Random();
int IP = 2 + rnd1.Next(10) + rnd1.Next(20) + rnd2.Next(30);
string vgetNextLocalIP = “127.0.0.” + IP;
return vgetNextLocalIP;
}

[Microsoft.SqlServer.Server.SqlFunction(
FillRowMethodName = “EssXmlaServices_FillRow”,
TableDefinition =
“Dim01 nvarchar(80) ,Dim02 nvarchar(80) ,Dim03 nvarchar(80) ,Dim04 nvarchar(80) ,” +
“Dim05 nvarchar(80) ,Dim06 nvarchar(80) ,Dim07 nvarchar(80) ,Dim08 nvarchar(80) ,Dim09 nvarchar(80) ,” +
“Dim10 nvarchar(80) ,Dim11 nvarchar(80) ,Dim12 nvarchar(80) ,Dim13 nvarchar(80) ,Dim14 nvarchar(80) ,” +
“Dim15 nvarchar(80) ,Dim16 nvarchar(80) ,Dim17 nvarchar(80) ,Dim18 nvarchar(80) ,Dim19 nvarchar(80) ,” +
“Dim20 nvarchar(80) ,Dim21 nvarchar(80) ,Dim22 nvarchar(80) ,Dim23 nvarchar(80) ,Dim24 nvarchar(80) ,” +
“Dim25 nvarchar(80) ,Dim26 nvarchar(80) ,Dim27 nvarchar(80) ,Dim28 nvarchar(80) ,Dim29 nvarchar(80) ,” +
“Dim30 nvarchar(80) ,StrValue nvarchar(80) ,DblValue decimal(32,9) ” /* /* ,XmlaRowValue nvarchar(4000) */
)]
public static IEnumerable BuildXmlaRowValuesArray(string vIP, string vLogin, string vPassword, string vTypeq, string vQueryBody)//, string vLogin, string vPassword ) //strMdxQuery, vLogin, vPassword,vEssBaseSRV, vXMLAUrl
{

try
{
string vXMLABody = “”;
// string vURL = “http://127.0.0.1:9000/aps/XMLA”;

string vURL = “http://”+vIP+”:13080/aps/XMLA”;

string vMdxQuery = vQueryBody;
vMdxQuery = vMdxQuery.ToUpper();

if (vTypeq.Contains(“mdx”))
{
vMdxQuery = vMdxQuery.Replace(“\t”, ” “);
vMdxQuery = vMdxQuery.Replace(” “, ” “);
vMdxQuery = vMdxQuery.Replace(” “, ” “);
vMdxQuery = vMdxQuery.Replace(” “, ” “);

if (!(vMdxQuery.IndexOf(“NONEMPTYBLOCK”, StringComparison.OrdinalIgnoreCase) > 0))
{
vMdxQuery = vMdxQuery.Replace(“ON COLUMNS,”, ” ON COLUMNS, NONEMPTYBLOCK”);
}

vMdxQuery = vMdxQuery.Replace(“NON EMPTY”, “”);
vMdxQuery = vMdxQuery.Replace(“ON COLUMNS,”, ” ON COLUMNS, NON EMPTY “);

vMdxQuery = vMdxQuery.Replace(“DIMENSION PROPERTIES MEMBER_NAME”, “”);
vMdxQuery = vMdxQuery.Replace(“ON ROWS”, ” DIMENSION PROPERTIES MEMBER_NAME ON ROWS “);

vMdxQuery = vMdxQuery.Replace(” “, ” “);
vMdxQuery = vMdxQuery.Replace(” “, ” “);
vMdxQuery = vMdxQuery.Replace(” “, ” “);

}

if (vTypeq.Contains(“mdxLev0”))
{
if (vMdxQuery.IndexOf(“MEMBER_NAME”, StringComparison.OrdinalIgnoreCase) > 0)
{
if (!(vMdxQuery.IndexOf(“GEN_NUMBER”, StringComparison.OrdinalIgnoreCase) > 0))
{ vMdxQuery = vMdxQuery.Replace(“MEMBER_NAME”, “MEMBER_NAME,GEN_NUMBER”); }
}

}

if (vTypeq.Contains(“Aso”))
{
vMdxQuery = vMdxQuery.Replace(“NONEMPTYBLOCK”, ” “);
}

if (vTypeq.Contains(“mdx”) || vTypeq.Contains(“mdxLev0”))
{
vXMLABody = “” +
” \n \n \n ” +
” \n ” + vMdxQuery + ” \n \n
” +
” \n ” +
” \n Provider=Essbase; Data Source=” + getNextLocalIP() + “\n ” +
” \nData \nTabular ” +
” \nTupleFormat \n 30000 ” +
” \n
\n
” +
” \n
” +
” \n
” +
” \n
“;
}
/*Убиваем кэширование*/
HttpRequestCachePolicy noCachePolicy = new HttpRequestCachePolicy(HttpRequestCacheLevel.NoCacheNoStore);

WebRequest request = WebRequest.Create(vURL);
request.CachePolicy = noCachePolicy;
/*Заканчиваем убивать кэширование*/

request.Credentials = new NetworkCredential(vLogin, vPassword);
request.Method = “POST”;
byte[] byteArray = Encoding.ASCII.GetBytes(vXMLABody);
request.ContentType = “text/xml; charset=windows-1251”;
request.ContentLength = byteArray.Length;
Stream dataStream = request.GetRequestStream();
dataStream.Write(byteArray, 0, byteArray.Length);
dataStream.Close();

WebResponse response = request.GetResponse();

// Console.WriteLine(((HttpWebResponse)response).StatusDescription);
dataStream = response.GetResponseStream();
StreamReader reader = new StreamReader(dataStream);
ArrayList XmlaResultCollection = new ArrayList();
String vTextBuffer = “”;
String vTextBuffer2 = “”;
String vClobBuffer = “”;
String vErrBuffer = “”;
String vStrBuff = “”;

int vPos = 0;
int vPos2 = 0;
int i = 1;
int isError = 1;
int modI = i % 2;
vPos = -1;
int isData = 1;
while (vPos <= -1 && isData == 1) { vTextBuffer = reader.ReadLine(); vClobBuffer = vClobBuffer + vTextBuffer; vPos = vClobBuffer.IndexOf("row>“, StringComparison.OrdinalIgnoreCase);
if (String.IsNullOrEmpty(vTextBuffer))
{
isData = 0;
}
}
if (vClobBuffer.Length > 0)
{
isData = 1;
}
vErrBuffer = vClobBuffer;
XmlaResult vCurrXmlaResult;

while (isData == 1 && vPos > 0)
{
modI = i % 2;
if (modI == 0)
{
vTextBuffer = vClobBuffer.Substring(1, vPos – 2);
vTextBuffer = vTextBuffer.Replace(“\n”, “”);
vTextBuffer = vTextBuffer.Replace(“\r”, “”);
vTextBuffer = vTextBuffer.Replace(“><", ""); vPos2 = vTextBuffer.IndexOf(">“, StringComparison.OrdinalIgnoreCase);
vStrBuff = “”;
while (!(vPos2 <= 0)) { vTextBuffer = vTextBuffer.Substring(vPos2 + 1); vTextBuffer2 = vTextBuffer.Substring(0, vTextBuffer.IndexOf("<", StringComparison.OrdinalIgnoreCase)); vStrBuff = vStrBuff + vTextBuffer2.Replace(";", ""); vStrBuff = vStrBuff + "|"; vPos2 = vTextBuffer.IndexOf(">“, StringComparison.OrdinalIgnoreCase);
};
vCurrXmlaResult = new XmlaResult(vStrBuff, vTypeq);

XmlaResultCollection.Add(new XmlaResult(vStrBuff, vTypeq));

isError = 0;
vErrBuffer = “”;
};
vClobBuffer = vClobBuffer.Substring(vPos + 4);
vPos = vClobBuffer.IndexOf(“row>”, StringComparison.OrdinalIgnoreCase);
isData = 1;
while (vPos <= -1 && isData == 1) { vTextBuffer = reader.ReadLine(); if (String.IsNullOrEmpty(vTextBuffer)) { isData = 0; } vClobBuffer = vClobBuffer + vTextBuffer; if (isError == 1) { vErrBuffer = vErrBuffer + "\n" + vTextBuffer; } vPos = vClobBuffer.IndexOf("row>“, StringComparison.OrdinalIgnoreCase);

}
isData = 1;
if (String.IsNullOrEmpty(vClobBuffer))
{
isData = 0;
}
i = i + 1;
}
reader.Close();
dataStream.Close();
response.Close();
if (isError == 1)
{
throw new ArgumentException(“This is query return no data and have this exception:\n”, vErrBuffer + “\n\n XMLABODY” + vXMLABody);
}

XmlaResultCollectionGlobal.Clear();
XmlaResultCollectionGlobal.AddRange(XmlaResultCollection);
XmlaResultCollection.Clear();

return XmlaResultCollectionGlobal;

}
catch (Exception e)
{
throw new ArgumentException(“\n \n Error in CLR Function:\n vErrorStep”, e.GetType().FullName + “\n ” + e.Source + “\n ” + e.StackTrace + “\n ” + e.TargetSite + “\n ” + e.Message + “\n \n”);

}

}

public static void EssXmlaServices_FillRow(object objXmlaResult, out SqlString dim01, out SqlString dim02,
out SqlString dim03, out SqlString dim04, out SqlString dim05, out SqlString dim06,
out SqlString dim07, out SqlString dim08, out SqlString dim09, out SqlString dim10,
out SqlString dim11, out SqlString dim12, out SqlString dim13, out SqlString dim14,
out SqlString dim15, out SqlString dim16, out SqlString dim17, out SqlString dim18,
out SqlString dim19, out SqlString dim20, out SqlString dim21, out SqlString dim22,
out SqlString dim23, out SqlString dim24, out SqlString dim25, out SqlString dim26,
out SqlString dim27, out SqlString dim28, out SqlString dim29, out SqlString dim30,
/*out SqlString strValue,*/ out SqlDecimal dblValue/*, out SqlString xmlaRowValue*/)
{
XmlaResult xmlaResult = (XmlaResult)objXmlaResult;
dblValue = xmlaResult.DblValue;
dim01 = xmlaResult.Dim01;
dim02 = xmlaResult.Dim02;
dim03 = xmlaResult.Dim03;
dim04 = xmlaResult.Dim04;
dim05 = xmlaResult.Dim05;
dim06 = xmlaResult.Dim06;
dim07 = xmlaResult.Dim07;
dim08 = xmlaResult.Dim08;
dim09 = xmlaResult.Dim09;
dim10 = xmlaResult.Dim10;
dim11 = xmlaResult.Dim11;
dim12 = xmlaResult.Dim12;
dim13 = xmlaResult.Dim13;
dim14 = xmlaResult.Dim14;
dim15 = xmlaResult.Dim15;
dim16 = xmlaResult.Dim16;
dim17 = xmlaResult.Dim17;
dim18 = xmlaResult.Dim18;
dim19 = xmlaResult.Dim19;
dim20 = xmlaResult.Dim20;
dim21 = xmlaResult.Dim21;
dim22 = xmlaResult.Dim22;
dim23 = xmlaResult.Dim23;
dim24 = xmlaResult.Dim24;
dim25 = xmlaResult.Dim25;
dim26 = xmlaResult.Dim26;
dim27 = xmlaResult.Dim27;
dim28 = xmlaResult.Dim28;
dim29 = xmlaResult.Dim29;
dim30 = xmlaResult.Dim30;
}
}