public MeasureByWeek(AdomdDataReader reader, string measure)
{
var member = reader["[Time].[Week].[Week].[MEMBER_CAPTION]"] as string;
var weekStr = member.Substring(_weekPrefix);
Week = DateTime.Parse(weekStr);
Values = reader.IsDBNull(reader.GetOrdinal(measure)) ? (double?)null : double.Parse(reader[measure].ToString());
}
/// <summary>
/// Returns a dictionary of row label to value retrieved from a query (command) applied to a cube (CubeInfo).
/// </summary>
/// <param name="cubeInfo"></param>
/// <param name="command"></param>
/// <returns></returns>
public Dictionary <string, double> GetSlicedMeasureData(CubeInfo cubeInfo, string command)
{
Dictionary <string, double> datalist = new Dictionary <string, double>();
string connection = cubeInfo.getStandardConnectionString();
try
{
using (AdomdConnection conn = new AdomdConnection(connection))
{
conn.Open();
using (AdomdCommand cmd = conn.CreateCommand())
{
cmd.CommandText = command;
using (AdomdDataReader result = cmd.ExecuteReader())
{
while (result.Read())
{
string label = "";
if (!result.IsDBNull(0))
{
label = result[0].ToString();
}
double value = 0;
if (!result.IsDBNull(2))
{
value = result.GetDouble(2);
}
datalist.Add(label, value);
}
}
}
conn.Close();
}
}
catch (AdomdErrorResponseException aere)
{
Utility.WriteToConsole(string.Format(Constants.invalid, aere.Message), 100);
}
return(datalist);
}
public HttpResponseMessage comparativa(DatosComparar comp)
{
string ROWS = @"NON EMPTY { [Dim Tiempo].[Dim Tiempo Año].CHILDREN } * { [Dim Tiempo].[Dim Tiempo Mes Siglas].CHILDREN } ON ROWS ";
string CUBO_NAME = @"[DWH Northwind] ";
string WHERE = "";
string dimension = @"";
string coma = "";
foreach (var dato in comp.Nombres)
{
switch (comp.dimension)
{
case 1: dimension += coma + @"[Dim Cliente].[Dim Cliente Nombre].&[" + dato + "]"; break;
case 2: dimension += coma + @"[Dim Producto].[Dim Producto Nombre].&[" + dato + "]"; break;
case 3: dimension += coma + @"[Dim Producto].[Dim Producto Categoria].&[" + dato + "]"; break;
case 4: dimension += coma + @"[Dim Empleado].[Dim Empleado Nombre].&[" + dato + "]"; break;
}
coma = ", ";
}
string COLUMNS = @" NON EMPTY { [Measures].[Fact Ventas Netas] } * { " + dimension + " } ON COLUMNS,";
string MDXQuery = "SELECT " + COLUMNS + ROWS + "FROM " + CUBO_NAME + WHERE;
List <Comparativa> ventasUsuario = new List <Comparativa>();
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn))
{
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
int fi = dr.FieldCount;
while (dr.Read())
{
Comparativa v = new Comparativa();
List <decimal> listaUsu = new List <decimal>();
v.fecha = dr.GetString(1) + " " + dr.GetString(0);
for (int i = 2; i < fi; i++)
{
decimal valor = dr.IsDBNull(i) ? 0 : dr.GetDecimal(i);
listaUsu.Add(valor);
}
v.Datos = listaUsu;
ventasUsuario.Add(v);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, ventasUsuario));
}
private static string stringify(AdomdDataReader row, int i)
{
if (row.IsDBNull(i))
{
return(null);
}
var type = row.GetFieldType(i);
if (type == typeof(byte[]) || type == typeof(char[]))
{
return(Convert.ToBase64String((byte[])row.GetValue(i)));
}
else if (type == typeof(IDataReader))
{
return("<IDataReader>");
}
return(row.GetValue(i).ToString());
}
public Task OutputResultsAsync(IQueryRunner runner)
{
var dlg = new Microsoft.Win32.SaveFileDialog
{
DefaultExt = ".txt",
Filter = "Tab separated text file|*.txt|Comma separated text file - UTF8|*.csv|Comma separated text file - Unicode|*.csv"
};
string fileName = "";
// Show save file dialog box
var result = dlg.ShowDialog();
// Process save file dialog box results
if (result == true)
{
// Save document
fileName = dlg.FileName;
return(Task.Run(() =>
{
try
{
runner.OutputMessage("Query Started");
var sw = Stopwatch.StartNew();
string sep = "\t";
string decimalSep = System.Globalization.CultureInfo.CurrentUICulture.NumberFormat.CurrencyDecimalSeparator;
string isoDateFormat = string.Format("yyyy-MM-dd HH:mm:ss{0}000", decimalSep);
Encoding enc = Encoding.UTF8;
switch (dlg.FilterIndex)
{
case 1: // tab separated
sep = "\t";
break;
case 2: // utf-8 csv
sep = System.Globalization.CultureInfo.CurrentUICulture.TextInfo.ListSeparator;
break;
case 3: //unicode csv
enc = Encoding.Unicode;
sep = System.Globalization.CultureInfo.CurrentUICulture.TextInfo.ListSeparator;
break;
}
var dq = runner.QueryText;
//var res = runner.ExecuteDataTableQuery(dq);
AdomdDataReader res = runner.ExecuteDataReaderQuery(dq);
if (res != null)
{
sw.Stop();
var durationMs = sw.ElapsedMilliseconds;
//runner.ResultsTable = res;
runner.OutputMessage("Command Complete, writing output file");
var sbLine = new StringBuilder();
bool moreResults = true;
int iFileCnt = 1;
while (moreResults)
{
int iMaxCol = res.FieldCount - 1;
int iRowCnt = 0;
if (iFileCnt > 1)
{
fileName = AddFileCntSuffix(fileName, iFileCnt);
}
using (var writer = new StreamWriter(File.Open(fileName, FileMode.Create), enc))
{
// write out clean column names
writer.WriteLine(string.Join(sep, res.CleanColumnNames()));
// write out data
while (res.Read())
{
iRowCnt++;
for (int iCol = 0; iCol < res.FieldCount; iCol++)
{
switch (res.GetDataTypeName(iCol))
{
case "Decimal":
case "Int64":
if (!res.IsDBNull(iCol))
{
sbLine.Append(res.GetString(iCol));
}
break;
case "DateTime":
if (res.IsDBNull(iCol))
{
sbLine.Append("\"\"");
}
else
{
sbLine.Append(res.GetDateTime(iCol).ToString(isoDateFormat));
} // ISO date format
break;
default:
sbLine.Append("\"");
if (!res.IsDBNull(iCol))
{
sbLine.Append(res.GetString(iCol).Replace("\"", "\"\"").Replace("\n", " "));
}
sbLine.Append("\"");
break;
}
if (iCol < iMaxCol)
{
sbLine.Append(sep);
}
}
writer.WriteLine(sbLine);
sbLine.Clear();
if (iRowCnt % 1000 == 0)
{
runner.NewStatusBarMessage(string.Format("Written {0:n0} rows to the file output", iRowCnt));
}
}
}
runner.OutputMessage(
string.Format("Query Completed ({0:N0} row{1} returned)"
, iRowCnt
, iRowCnt == 1 ? "" : "s"), durationMs);
runner.RowCount = iRowCnt;
moreResults = res.NextResult();
iFileCnt++;
}
runner.SetResultsMessage("Query results written to file", OutputTargets.Grid);
//runner.QueryCompleted();
runner.ActivateOutput();
}
res.Close();
}
catch (Exception ex)
{
runner.ActivateOutput();
runner.OutputError(ex.Message);
#if DEBUG
runner.OutputError(ex.StackTrace);
#endif
}
finally
{
runner.QueryCompleted();
}
}));
}
// else dialog was cancelled so return an empty task.
return(Task.Run(() => { }));
}
public void LoadData(AdomdDataReader reader)
{
ClearGrid();
int iColCount = reader.FieldCount;
this.ColumnsCount = iColCount;
this.RowsCount = 1;
// set the column headers
for (int i = 0; i < iColCount; i++)
{
string sColumn = reader.GetName(i);
SetHeaderValue(0, i, sColumn);
}
int iRow = 1;
this.RowsCount = 1;
while (reader.Read())
{
this.RowsCount++;
for(int iCol=0; iCol < iColCount; iCol++)
{
if (!reader.IsDBNull(iCol))
{
string sValue = reader.GetValue(iCol).ToString();
SetCellValue(iRow, iCol, sValue);
}
else
{
SetCellValue(iRow, iCol, "");
}
}
iRow++;
}
}
public MeasureByMonth(AdomdDataReader reader, string measure)
{
var member = reader["[Time].[Month].[Month].[MEMBER_CAPTION]"] as string;
Month = DateTime.ParseExact(member, "MMMM yyyy", CultureInfo.InvariantCulture);
Values = reader.IsDBNull(reader.GetOrdinal(measure)) ? (double?)null : double.Parse(reader[measure].ToString());
}