public HttpResponseMessage GetDataPieByDimension(string dimension, string order, string[] values)
{
string elementsToSearch = "";
foreach (var item in values)
{
elementsToSearch += ($@"{dimension}.&[{item}],");
}
elementsToSearch = elementsToSearch.Remove(elementsToSearch.Length - 1);
string MDX_QUERY = $@"
SELECT
({{ {elementsToSearch} }})
ON ROWS,{{
[Measures].[VENTAS]
}}ON COLUMNS FROM [PUBS DW]
";
List <string> dim = new List <string>();
List <decimal> ventas = new List <decimal>();
List <dynamic> lstTabla = new List <dynamic>();
dynamic result = new
{
datosDimension = dim,
datosVenta = ventas,
datosTabla = lstTabla
};
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["cubopubs"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
//cmd.Parameters.Add("Dimension", valoresDimension);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
dim.Add(dr.GetString(0));
ventas.Add(dr.GetDecimal(1));
dynamic objTabla = new
{
descripcion = dr.GetString(0),
valor = dr.GetDecimal(1)
};
lstTabla.Add(objTabla);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
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));
}
public HttpResponseMessage Top(Reporte info)
{
string nombreDimension = @"";
switch (info.dimension)
{
case 1: //Clientes
default:
nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].CHILDREN } ";
break;
case 2: //Productos
nombreDimension = @"{ [Dim Producto].[Dim Producto Nombre].CHILDREN } ";
break;
case 3: //Categorias
nombreDimension = @"{ [Dim Producto].[Dim Producto Categoria].CHILDREN } ";
break;
case 4: //Empleadoss
nombreDimension = @"{ [Dim Empleado].[Dim Empleado Nombre].CHILDREN } ";
break;
}
string WITH = @"WITH SET [TopVentas] AS NONEMPTY( ORDER (STRTOSET(@Dimension), [Measures].[Fact Ventas Netas], BDESC ) ) ";
string COLUMNS = @"NON EMPTY { [Measures].[Fact Ventas Netas] } ON COLUMNS, ";
string ROWS = @"NON EMPTY { HEAD([TopVentas], " + info.top + ") } ON ROWS ";
string CUBO_NAME = @"[DWH Northwind] ";
string MDXQuery = WITH + "SELECT " + COLUMNS + ROWS + "FROM " + CUBO_NAME;
List <Venta> ventasUsuario = new List <Venta>();
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn))
{
cmd.Parameters.Add(new AdomdParameter("Dimension", nombreDimension));
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
Venta v = new Venta();
v.mes = dr.GetString(0);
v.ventas = dr.GetDecimal(1);
ventasUsuario.Add(v);
}
dr.Close();
}
}
};
return(Request.CreateResponse(HttpStatusCode.OK, ventasUsuario));
}
public HttpResponseMessage ventas(Filtro info)
{
//string WITH = @"WITH SET [TopVentas] AS NONEMPTY( ORDER (STRTOSET(@Dimension), [Measures].[Fact Ventas Netas], BDESC ) ) ";
string COLUMNS = @" NON EMPTY { [Measures].[Fact Ventas Netas] } ON COLUMNS,";
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 = "";
if (info.Item != 0 && info.Nombre != "")
{
string dimension = @"";
switch (info.Item)
{
case 1: dimension = @"[Dim Cliente].[Dim Cliente Nombre].&[" + info.Nombre + "]"; break;
case 2: dimension = @"[Dim Producto].[Dim Producto Nombre].&[" + info.Nombre + "]"; break;
case 3: dimension = @"[Dim Producto].[Dim Producto Categoria].&[" + info.Nombre + "]"; break;
case 4: dimension = @"[Dim Empleado].[Dim Empleado Nombre].&[" + info.Nombre + "]"; break;
}
WHERE = " WHERE " + dimension;
}
string MDXQuery = "SELECT " + COLUMNS + ROWS + "FROM " + CUBO_NAME + WHERE;
List <Venta> ventasUsuario = new List <Venta>();
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))
{
while (dr.Read())
{
Venta v = new Venta();
//v.ano = ;
v.mes = dr.GetString(1) + " " + dr.GetInt16(0);
v.ventas = dr.GetDecimal(2);
ventasUsuario.Add(v);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, ventasUsuario));
}
public HttpResponseMessage GetDataBar(string[] values)
{
string WITH = @"
WITH
SET [OrderDimension] AS
NONEMPTY(
ORDER(
STRTOSET(@Dimension),
[Measures].[Ventas], DESC
)
)
";
string COLUMNS = @"
NON EMPTY
{
[Measures].[Ventas]
}
ON COLUMNS,
";
string ROWS = @"
NON EMPTY
{
[OrderDimension]
}
ON ROWS
";
string CUBO_NAME = "[DHW Northwind]";
string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
Debug.Write(MDX_QUERY);
List <string> dimension = new List <string>();
List <decimal> ventas = new List <decimal>();
List <dynamic> lstTabla = new List <dynamic>();
dynamic result = new
{
datosDimension = dimension,
datosVenta = ventas,
datosTabla = lstTabla
};
string valoresDimension = string.Empty;
foreach (var item in values)
{
if (item == "1996" || item == "1997" || item == "1998")
{
valoresDimension += "[Dim Tiempo].[Dim Tiempo Año].[" + item + "],";
}
else if (item == "Enero" || item == "Febrero" || item == "Marzo" || item == "Abril" || item == "Mayo" || item == "Junio" || item == "Julio" ||
item == "Agosto" || item == "Septiembre" || item == "Octubre" || item == "Noviembre" || item == "Diciembre")
{
valoresDimension += "[Dim Tiempo].[Dim Tiempo Mes].[" + item + "],";
}
else
{
valoresDimension += "[Dim Cliente].[Dim Cliente Nombre].[" + item + "],";
}
}
valoresDimension = valoresDimension.TrimEnd(',');
valoresDimension = string.Format(valoresDimension);
valoresDimension = @"{" + valoresDimension + "}";
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
cmd.Parameters.Add("Dimension", valoresDimension);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
dimension.Add(dr.GetString(0));
ventas.Add(Math.Round(dr.GetDecimal(1)));
dynamic objTabla = new
{
descripcion = dr.GetString(0),
valor = Math.Round(dr.GetDecimal(1))
};
lstTabla.Add(objTabla);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
public HttpResponseMessage Histograma(string dim, string order = "DESC")
{
string dimension;
List <string> clients = new List <string>();
List <string> employees = new List <string>();
List <string> products = new List <string>();
List <int> years = new List <int>();
switch (dim)
{
case "Cliente":
dimension = "[Dim Cliente].[Dim Cliente Nombre].CHILDREN";
break;
case "Producto":
dimension = "[Dim Producto].[Dim Producto Nombre].CHILDREN";
break;
case "Empleado":
dimension = "[Dim Empleado].[Dim Empleado Nombre].CHILDREN";
break;
case "Año":
dimension = "[Dim Tiempo].[Dim Tiempo Año].CHILDREN";
break;
default:
dimension = "[Dim Cliente].[Dim Cliente Nombre].CHILDREN";
break;
}
string WITH = @"
WITH
SET [TopVentas] AS
NONEMPTY(
ORDER(
STRTOSET(@Dimension),
[Measures].[Ventas], " + order +
@")
)
";
string COLUMNS = @"
NON EMPTY
{
[Measures].[Ventas]
}
ON COLUMNS,
";
string ROWS = @"
NON EMPTY
{
HEAD([TopVentas], 10)
}
ON ROWS
";
string CUBO_NAME = "[DHW Northwind]";
string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
Debug.Write(MDX_QUERY);
List <decimal> ventas = new List <decimal>();
List <dynamic> lstTabla = new List <dynamic>();
dynamic result2;
switch (dim)
{
case "Cliente":
result2 = new{
datosDimension = clients,
datosVenta = ventas,
datosTabla = lstTabla
};
break;
case "Producto":
result2 = new
{
datosDimension = products,
datosVenta = ventas,
datosTabla = lstTabla
};
break;
case "Empleado":
result2 = new
{
datosDimension = employees,
datosVenta = ventas,
datosTabla = lstTabla
};
break;
case "Año":
result2 = new
{
datosDimension = years,
datosVenta = ventas,
datosTabla = lstTabla
};
break;
default:
result2 = new
{
datosDimension = clients,
datosVenta = ventas,
datosTabla = lstTabla
};
break;
}
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
cmd.Parameters.Add("Dimension", dimension);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
switch (dim)
{
case "Cliente":
clients.Add(dr.GetString(0));
break;
case "Producto":
products.Add(dr.GetString(0));
break;
case "Empleado":
employees.Add(dr.GetString(0));
break;
case "Año":
years.Add(dr.GetInt32(0));
break;
default:
clients.Add(dr.GetString(0));
break;
}
ventas.Add(Math.Round(dr.GetDecimal(1)));
dynamic objTabla = new
{
descripcion = dr.GetString(0),
valor = Math.Round(dr.GetDecimal(1))
};
lstTabla.Add(objTabla);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result2));
}
public HttpResponseMessage Top5(string dimension, int order, int top)
{
string orderString = "";
switch (order)
{
case 0:
orderString = "ASC";
break;
case 1:
orderString = "DESC";
break;
default:
orderString = "ASC";
break;
}
string topString = "";
if (top > 0)
{
topString = top.ToString();
}
else
{
topString = dimension;
}
var mdxQuery = $@"
WITH SET [TopVentas] AS
NONEMPTY(
ORDER(
{{ {dimension}.CHILDREN}},
[Measures].[VENTAS],{orderString}))
SELECT NON EMPTY
{{
([Measures].[VENTAS])
}} ON COLUMNS , NON EMPTY
{{
HEAD(TopVentas, {topString} )
}} ON ROWS
FROM [PUBS DW]";
List <string> dim = new List <string>();
List <decimal> ventas = new List <decimal>();
List <dynamic> lstTabla = new List <dynamic>();
dynamic result = new
{
datosDimension = dim,
datosVenta = ventas,
datosTabla = lstTabla
};
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["cubopubs"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(mdxQuery, cnn))
{
//cmd.Parameters.Add("Dimension", valoresDimension);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
dim.Add(dr.GetString(0));
ventas.Add(dr.GetDecimal(1));
dynamic objTabla = new
{
descripcion = dr.GetString(0),
valor = dr.GetDecimal(1)
};
lstTabla.Add(objTabla);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
public HttpResponseMessage GetDataByDimension(string dim, [FromBody] dynamic values)
{
string WITH = @"
WITH
SET [OrderDimension] AS
NONEMPTY(
ORDER(
STRTOSET(@Dimension),
[Measures].[Ventas], DESC
)
)
";
string COLUMNS = @"
NON EMPTY
{
[Measures].[Ventas]
}
ON COLUMNS,
";
string ROWS = @"
NON EMPTY
{
([OrderDimension], STRTOSET(@Years), STRTOSET(@Months))
}
ON ROWS
";
string CUBO_NAME = "[DWH Northwind]";
string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
Debug.Write(MDX_QUERY);
List <string> dimension = new List <string>();
List <string> years = new List <string>();
List <string> months = new List <string>();
List <decimal> sales = new List <decimal>();
List <dynamic> tableList = new List <dynamic>();
dynamic result = new
{
dimensionData = dimension,
yearsData = years,
monthsData = months,
salesData = sales,
tableData = tableList
};
string dimensionValues = string.Empty;
Console.WriteLine(values);
foreach (var item in values.clients)
{
dimensionValues += "{0}.[" + item + "],";
}
dimensionValues = dimensionValues.TrimEnd(',');
dimensionValues = string.Format(dimensionValues, dim);
dimensionValues = @"{" + dimensionValues + "}";
string yearsValues = string.Empty;
foreach (var item in values.years)
{
yearsValues += "[Dim Tiempo].[Año].[" + item + "],";
}
yearsValues = yearsValues.TrimEnd(',');
yearsValues = @"{" + yearsValues + "}";
string monthsValues = string.Empty;
foreach (var item in values.months)
{
monthsValues += "[Dim Tiempo].[Mes].[" + item + "],";
}
monthsValues = monthsValues.TrimEnd(',');
monthsValues = @"{" + monthsValues + "}";
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
cmd.Parameters.Add("Dimension", dimensionValues);
cmd.Parameters.Add("Years", yearsValues);
cmd.Parameters.Add("Months", monthsValues);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
dimension.Add(dr.GetString(0));
years.Add(dr.GetString(1));
months.Add(dr.GetString(2));
sales.Add(Math.Round(dr.GetDecimal(3)));
dynamic objTable = new
{
description = dr.GetString(0),
years = dr.GetString(1),
months = dr.GetString(2),
valor = Math.Round(dr.GetDecimal(3))
};
tableList.Add(objTable);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
public HttpResponseMessage GetDataPieByDimension(string dim, string order, [FromBody] dynamic values)
{
string WITH = @"
WITH
SET [OrderDimension] AS
NONEMPTY(
ORDER(
STRTOSET(@Dimension),
[Measures].[Fact Ventas Netas], DESC
)
)
";
string COLUMNS = @"
NON EMPTY
{
[Measures].[Fact Ventas Netas]
}
ON COLUMNS,
";
string ROWS = @"
NON EMPTY
{
([OrderDimension], STRTOSET(@Anios), STRTOSET(@Meses))
}
ON ROWS
";
string CUBO_NAME = "[DWH Northwind]";
//WITH = string.Format(WITH, dim);
string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
Debug.Write(MDX_QUERY);
List <string> dimension = new List <string>();
List <string> anios = new List <string>();
List <string> meses = new List <string>();
List <decimal> ventas = new List <decimal>();
List <dynamic> lstTabla = new List <dynamic>();
dynamic result = new
{
datosDimension = dimension,
datosAnios = anios,
datosMeses = meses,
datosVenta = ventas,
datosTabla = lstTabla
};
string valoresDimension = string.Empty;
Console.WriteLine(values);
foreach (var item in values.clients)
{
valoresDimension += "{0}.[" + item + "],";
}
valoresDimension = valoresDimension.TrimEnd(',');
valoresDimension = string.Format(valoresDimension, dim);
valoresDimension = @"{" + valoresDimension + "}";
string valoresAnios = string.Empty;
foreach (var item in values.years)
{
valoresAnios += "[Dim Tiempo].[Dim Tiempo Año].[" + item + "],";
}
valoresAnios = valoresAnios.TrimEnd(',');
valoresAnios = @"{" + valoresAnios + "}";
string valoresMeses = string.Empty;
foreach (var item in values.months)
{
valoresMeses += "[Dim Tiempo].[Dim Tiempo Mes].[" + item + "],";
}
valoresMeses = valoresMeses.TrimEnd(',');
valoresMeses = @"{" + valoresMeses + "}";
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
cmd.Parameters.Add("Dimension", valoresDimension);
cmd.Parameters.Add("Anios", valoresAnios);
cmd.Parameters.Add("Meses", valoresMeses);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
dimension.Add(dr.GetString(0));
anios.Add(dr.GetString(1));
meses.Add(dr.GetString(2));
ventas.Add(Math.Round(dr.GetDecimal(3)));
dynamic objTabla = new
{
descripcion = dr.GetString(0),
años = dr.GetString(1),
meses = dr.GetString(2),
valor = Math.Round(dr.GetDecimal(3))
};
lstTabla.Add(objTabla);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
public HttpResponseMessage TablaComparativa(Filtro objFiltro)
{
string nombreDimension = @"";
switch (objFiltro.Dimension)
{
case "1": // Clientes
nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].&[" + objFiltro.Item + "] } ";
break;
case "2": // Productos
nombreDimension = @"{ [Dim Producto].[Dim Producto Nombre].&[" + objFiltro.Item + "] } ";
break;
case "3": // Categorías
nombreDimension = @"{ [Dim Producto].[Dim Producto Categoria].&[" + objFiltro.Item + "] } ";
break;
case "4": // Empleados
nombreDimension = @"{ [Dim Empleado].[Dim Empleado Nombre].&[" + objFiltro.Item + "] } ";
break;
}
string WITH = @"WITH
SET [TopVentas] AS
NONEMPTY(
ORDER(
STRTOSET(@Dimension),
[Measures].[Fact Ventas Netas], BDESC
)
)" ;
string COLUMNS = @"NON EMPTY
{
[Measures].[Fact Ventas Netas]
}
ON COLUMNS,";
string ROWS = @"NON EMPTY
{
(
[Dim Tiempo].[Dim Tiempo Mes Siglas].CHILDREN,
[Dim Tiempo].[Dim Tiempo Año].CHILDREN
)
}
*
{
HEAD([TopVentas],1000)
}
ON ROWS";
string CUBO_NAME = @"[DWH Northwind]";
string MDXQuery = WITH +
@"SELECT " +
COLUMNS +
ROWS +
" FROM " + CUBO_NAME;
List <string> fecha = new List <string>();
List <decimal> valor = new List <decimal>();
dynamic data;
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn))
{
cmd.Parameters.Add(new AdomdParameter("Dimension", nombreDimension));
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
fecha.Add(dr.GetString(0) + "-" + dr.GetString(1));
valor.Add(Math.Round(dr.GetDecimal(3)));
}
dr.Close();
}
}
data = new { fecha = fecha, valor = valor };
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)data));
}
public HttpResponseMessage Top5(string dimension)
{
string nombreDimension = @"";
switch (dimension)
{
case "1": // Clientes
nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].CHILDREN } ";
break;
case "2": // Productos
nombreDimension = @"{ [Dim Producto].[Dim Producto Nombre].CHILDREN } ";
break;
case "3": // Categorías
nombreDimension = @"{ [Dim Producto].[Dim Producto Categoria].CHILDREN } ";
break;
case "4": // Empleados
nombreDimension = @"{ [Dim Empleado].[Dim Empleado Nombre].CHILDREN } ";
break;
default: //Dimension por Default
nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].CHILDREN } ";
break;
}
string WITH = @"WITH
SET [TopVentas] AS
NONEMPTY(
ORDER(
STRTOSET(@Dimension),
[Measures].[Fact Ventas Netas], BDESC
)
)" ;
string COLUMNS = @"NON EMPTY
{
[Measures].[Fact Ventas Netas]
}
ON COLUMNS,";
string ROWS = @"NON EMPTY
{
HEAD([TopVentas],5)
}
ON ROWS";
string CUBO_NAME = @"[DWH Northwind]";
string MDXQuery = WITH +
@"SELECT " +
COLUMNS +
ROWS +
" FROM " + CUBO_NAME;
Dictionary <string, decimal> structure = new Dictionary <string, decimal>();
List <string> name = new List <string>();
List <decimal> value = new List <decimal>();
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn))
{
cmd.Parameters.Add(new AdomdParameter("Dimension", nombreDimension));
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
//structure.Add(dr.GetString(0), dr.GetDecimal(1));
name.Add(dr.GetString(0));
value.Add(dr.GetDecimal(1));
//Debug.Write(structure);
}
dr.Close();
}
}
}
var data = (name, value);
return(Request.CreateResponse(HttpStatusCode.OK, data));
}
public HttpResponseMessage SerieHistoricaPost(Filtro objFiltro)
{
//Debug.Write(objFiltro);
//return Request.CreateResponse(HttpStatusCode.OK, objFiltro);
//Se puso en filtro 0 para no tomarlo en cuenta y traer todas las de la categoría, sin embargo en el MDX no funciona, se trae todas las ventas
string nombreDimension = @"";
if (objFiltro.Item == "0")
{
switch (objFiltro.Dimension)
{
case "1": // Clientes
nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].&[QUICK-Stop], [Dim Cliente].[Dim Cliente Nombre].&[Ernst Handel], [Dim Cliente].[Dim Cliente Nombre].&[Save-a-lot Markets], [Dim Cliente].[Dim Cliente Nombre].&[Rattlesnake Canyon Grocery], [Dim Cliente].[Dim Cliente Nombre].&[Hungry Owl All-Night Grocers] } ";
break;
case "2": // Productos
nombreDimension = @"{ [Dim Producto].[Dim Producto Nombre].&[Côte de Blaye], [Dim Producto].[Dim Producto Nombre].&[Thüringer Rostbratwurst], [Dim Producto].[Dim Producto Nombre].&[Raclette Courdavault], [Dim Producto].[Dim Producto Nombre].&[Tarte au sucre], [Dim Producto].[Dim Producto Nombre].&[Camembert Pierrot] } ";
break;
case "3": // Categorías
nombreDimension = @"{ [Dim Producto].[Dim Producto Categoria].&[Beverages], [Dim Producto].[Dim Producto Categoria].&[Dairy Products], [Dim Producto].[Dim Producto Categoria].&[Confections], [Dim Producto].[Dim Producto Categoria].&[Meat/Poultry], [Dim Producto].[Dim Producto Categoria].&[Seafood] } ";
break;
case "4": // Empleados
nombreDimension = @"{ [Dim Empleado].[Dim Empleado Nombre].&[Margaret Peacock], [Dim Empleado].[Dim Empleado Nombre].&[Janet Leverling], [Dim Empleado].[Dim Empleado Nombre].&[Nancy Davolio], [Dim Empleado].[Dim Empleado Nombre].&[Andrew Fuller], [Dim Empleado].[Dim Empleado Nombre].&[Laura Callahan] } ";
break;
default: //Dimension por Default
nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].&[QUICK-Stop], [Dim Cliente].[Dim Cliente Nombre].&[Ernst Handel], [Dim Cliente].[Dim Cliente Nombre].&[Save-a-lot Markets], [Dim Cliente].[Dim Cliente Nombre].&[Rattlesnake Canyon Grocery], [Dim Cliente].[Dim Cliente Nombre].&[Hungry Owl All-Night Grocers] } ";
break;
}
}
else
{
if (objFiltro.Item == "Meat")
{
objFiltro.Item = "Meat/Poultry";
}
switch (objFiltro.Dimension)
{
case "1": // Clientes
nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].&[" + objFiltro.Item + "] } ";
break;
case "2": // Productos
nombreDimension = @"{ [Dim Producto].[Dim Producto Nombre].&[" + objFiltro.Item + "] } ";
break;
case "3": // Categorías
nombreDimension = @"{ [Dim Producto].[Dim Producto Categoria].&[" + objFiltro.Item + "] } ";
break;
case "4": // Empleados
nombreDimension = @"{ [Dim Empleado].[Dim Empleado Nombre].&[" + objFiltro.Item + "] } ";
break;
default: //Dimension por Default
nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].&[" + objFiltro.Item + "] } ";
break;
}
}
string COLUMNS = @"NON EMPTY
{
[Measures].[Fact Ventas Netas]
}
ON COLUMNS,";
string ROWS = @"NON EMPTY
{
[Dim Tiempo].[Dim Tiempo Año].CHILDREN
}
*
{
[Dim Tiempo].[Dim Tiempo Mes Siglas].CHILDREN
}
*
{
STRTOSET(@Dimension)
}
ON ROWS";
string CUBO_NAME = @"[DWH Northwind]";
string MDXQuery = @"SELECT " +
COLUMNS +
ROWS +
" FROM " + CUBO_NAME;
Dictionary <string, decimal> structure = new Dictionary <string, decimal>();
List <string> year = new List <string>();
List <string> month = new List <string>();
List <decimal> value = new List <decimal>();
List <string> filter = new List <string>();
dynamic data;
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn))
{
cmd.Parameters.Add(new AdomdParameter("Dimension", nombreDimension));
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
year.Add(dr.GetString(0));
month.Add(dr.GetString(1));
filter.Add(dr.GetString(2));
value.Add(Math.Round(dr.GetDecimal(3)));
}
dr.Close();
}
}
data = new { years = year, months = month, filters = filter, values = value };
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)data));
}
public HttpResponseMessage GetDataGeneric(string dim, string[] anios, string[] meses, string[] values)
{
string dimension = string.Empty;
switch (dim)
{
case "Cliente":
dimension = "[Dim Cliente].[Dim Cliente Nombre]";
break;
case "Producto":
dimension = "[Dim Producto].[Dim Producto Nombre]";
break;
case "Empleado":
dimension = "[Dim Empleado].[Dim Empleado Nombre]";
break;
default:
dimension = "[Dim Cliente].[Dim Cliente Nombre]";
break;
}
string WITH = @"
WITH
SET [Info] AS
NONEMPTY(
ORDER(
STRTOSET(@Dimension),
[Measures].[Hec Ventas Ventas], DESC
)
)
";
string COLUMNS = @"
{
STRTOSET(@Anio)
}
ON COLUMNS,
";
string ROWS = @"
{
[Info]
}*
{
STRTOSET(@Mes)
}
ON ROWS
";
string CUBO_NAME = "[DWH Northwind]";
string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
List <string> dimen = new List <string>();
List <decimal> ventas = new List <decimal>();
List <string> mes = new List <string>();
List <string> anio = new List <string>();
List <dynamic> lstTabla = new List <dynamic>();
dynamic result = new
{
datosDimension = dimen,
datosVenta = ventas,
datosMes = mes,
datosAnio = anio,
datosTabla = lstTabla
};
string valoresDimension = string.Empty;
foreach (var item in values)
{
valoresDimension += "{0}.[" + item + "],";
}
valoresDimension = valoresDimension.TrimEnd(',');
valoresDimension = string.Format(valoresDimension, dimension);
valoresDimension = @"{" + valoresDimension + "}";
string valoresMes = string.Empty;
foreach (var item in meses)
{
valoresMes += "[Dim Tiempo].[Mes Espaniol].[" + item + "],";
}
valoresMes = valoresMes.TrimEnd(',');
//valoresMes = @"{" + valoresMes + "}";
string valoresAnio = string.Empty;
foreach (var item in anios)
{
valoresAnio += "[Dim Tiempo].[Anio].[" + item + "],";
}
valoresAnio = valoresAnio.TrimEnd(',');
//valoresAnio = @"{" + valoresAnio + "}";
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
cmd.Parameters.Add("Dimension", valoresDimension);
cmd.Parameters.Add("Anio", valoresAnio);
cmd.Parameters.Add("Mes", valoresMes);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
dimen.Add(dr.GetString(0));
ventas.Add(Math.Round(dr.GetDecimal(1)));
mes.Add(dr.GetString(2));
anio.Add(dr.GetString(3));
dynamic objTabla = new
{
descripcion = dr.GetString(0),
data = Math.Round(dr.GetDecimal(1)),
label = dr.GetString(3)
};
lstTabla.Add(objTabla);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
public HttpResponseMessage GetItemByDimension(string dim)
{
string dimension = string.Empty;
string dim2 = string.Empty;
switch (dim)
{
case "Cliente":
dimension = "[Dim Cliente].[Dim Cliente Nombre].CHILDREN";
dim2 = "[Dim Cliente].[Dim Cliente Nombre].CURRENTMEMBER.MEMBER_NAME";
break;
case "Producto":
dimension = "[Dim Producto].[Dim Producto Nombre].CHILDREN";
dim2 = "[Dim Producto].[Dim Producto Nombre].CURRENTMEMBER.MEMBER_NAME";
break;
case "Empleado":
dimension = "[Dim Empleado].[Dim Empleado Nombre].CHILDREN";
dim2 = "[Dim Empleado].[Dim Empleado Nombre].CURRENTMEMBER.MEMBER_NAME";
break;
case "Tiempo":
dimension = "[Dim Tiempo].[Anio].CHILDREN";
dim2 = "[Dim Tiempo].[Anio].CURRENTMEMBER.MEMBER_NAME";
break;
default:
dimension = "[Dim Cliente].[Dim Cliente Nombre].CHILDREN";
dim2 = "[Dim Cliente].[Dim Cliente Nombre].CURRENTMEMBER.MEMBER_NAME";
break;
}
string WITH = @"
WITH
SET [OrderDImension] AS
NONEMPTY(
ORDER(
STRTOSET(@Dimension),
" + dim2 + @", ASC
)
)
";
string COLUMNS = @"
NON EMPTY
{
[Measures].[Hec Ventas Ventas]
}
ON COLUMNS,
";
string ROWS = @"
NON EMPTY
{
[OrderDImension]
}
ON ROWS
";
string CUBO_NAME = "[DWH Northwind]";
string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
List <string> clients = new List <string>();
List <decimal> ventas = new List <decimal>();
List <dynamic> lstTabla = new List <dynamic>();
dynamic result = new
{
datosDimension = clients,
datosVenta = ventas,
datosTabla = lstTabla
};
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
cmd.Parameters.Add("Dimension", dimension);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
clients.Add(dr.GetString(0));
ventas.Add(Math.Round(dr.GetDecimal(1)));
dynamic objTabla = new
{
descripcion = dr.GetString(0),
valor = Math.Round(dr.GetDecimal(1))
};
lstTabla.Add(objTabla);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
public HttpResponseMessage GetDataByDimension(string dim, string order, string[] values)
{
string WITH = @"
WITH
SET [OrderDimension] AS
NONEMPTY(
ORDER(
STRTOSET(@Dimension),
[Measures].[Hec Ventas Ventas], DESC
)
)
";
string COLUMNS = @"
NON EMPTY
{
[Measures].[Hec Ventas Ventas]
}
ON COLUMNS,
";
string ROWS = @"
NON EMPTY
{
[OrderDimension]
}
ON ROWS
";
string CUBO_NAME = "[DWH Northwind]";
string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
Debug.Write(MDX_QUERY);
List <string> dimension = new List <string>();
List <decimal> ventas = new List <decimal>();
List <dynamic> lstTabla = new List <dynamic>();
dynamic result = new
{
datosDimension = dimension,
datosVenta = ventas,
datosTabla = lstTabla
};
string valoresDimension = string.Empty;
foreach (var item in values)
{
valoresDimension += "{0}.[" + item + "],";
}
valoresDimension = valoresDimension.TrimEnd(',');
valoresDimension = string.Format(valoresDimension, dim);
valoresDimension = @"{" + valoresDimension + "}";
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
cmd.Parameters.Add("Dimension", valoresDimension);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
dimension.Add(dr.GetString(0));
ventas.Add(Math.Round(dr.GetDecimal(1)));
dynamic objTabla = new
{
descripcion = dr.GetString(0),
valor = Math.Round(dr.GetDecimal(1))
};
lstTabla.Add(objTabla);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}