public void creaTabla()
{
DBConnection db = new DBConnection(CadenaDeConexion);
db.connect();
StringBuilder cmd = new StringBuilder();
cmd.Append(" IF OBJECT_ID('");
cmd.Append(Esquema);
cmd.Append(".dbo.cube', 'U') IS NOT NULL");
cmd.Append(" DROP TABLE ");
cmd.Append(Esquema);
cmd.Append(".dbo.cube");
db.ejecutaComando(cmd.ToString());
cmd = new StringBuilder();
cmd.Append("Create table ");
cmd.Append(Esquema);
cmd.Append(".dbo.cube(");
for (int i = 0; i < campos.Count; i++)
{
if (i != 0)
{
cmd.Append(",");
}
cmd.Append(campos[i]);
cmd.Append(" ");
cmd.Append("Text");//tipos[i]);
}
if (campos.Count > 0)
{
cmd.Append(",");
}
if (tipoF == tipoFuncion.cont)
{
cmd.Append("Total");
}
else
{
cmd.Append(columnaResumen);
}
cmd.Append(" ");
cmd.Append("int");
cmd.Append(")");
String debug = cmd.ToString();
db.ejecutaComando(cmd.ToString());
}
private void generaQuery()
{
// agrupar por los que tienen 0, los
DBConnection db = new DBConnection(CadenaDeConexion);
db.connect();
StringBuilder cmd = new StringBuilder();
cmd.Append("IF OBJECT_ID('dbo.#temp', 'U') IS NOT NULL ");
cmd.Append(" DROP TABLE dbo.#temp ");
cmd.Append(" create table dbo.#temp ( ");
for(int i=0; i<todoCampo.Count;i++){
if(i!= 0){
cmd.Append(",");
}
cmd.Append(todoCampo[i]);
cmd.Append(" ");
cmd.Append(todoTipo[i]);
}
cmd.Append(" ) ");
cmd.Append(" Insert into dbo.#temp ");
cmd.Append(query);
/* generar tabla estilo lo siguiente
*
IF OBJECT_ID('dbo.#temp', 'U') IS NOT NULL DROP TABLE dbo.#temp
create table dbo.#temp ( usuario int,problema int,puntos int,hora bigint,primero int )
Insert into dbo.#temp SELECT * FROM [Karelotitlan].[dbo].[UsuarioProblema]
select CAST( CAST(usuario AS nvarchar) as text), CAST( CAST(problema AS nvarchar) AS text), sum(primero) from
dbo.#temp
group by usuario,problema
Insert into Karelotitlan.dbo.cube
select CAST( CAST(usuario AS nvarchar) as text), CAST( CAST(problema AS nvarchar) AS text), sum(primero) from
dbo.#temp
group by usuario,problema
drop table dbo.#temp
*
*/
cmd.Append(" Insert into ");
cmd.Append(Esquema);
cmd.Append(".dbo.cube ");
cmd.Append("select ");
// lista de parametreos si tiene 0 hacer CAST si no poner 0
for (int i = 0; i < campos.Count; i++)
{
if (i != 0)
{
cmd.Append(",");
}
if (elegidos[i] == 0)
{
//cmd.Append(" CAST ( CAST ( ");
cmd.Append(campos[i]);
//cmd.Append(" AS nvarchar) AS text) ");
}
else
{
cmd.Append(" '(ALL)' ");
}
}
if (campos.Count > 0)
{
cmd.Append(",");
}
switch (this.tipoF)
{
case tipoFuncion.cont:
cmd.Append(" count(*) ");
break;
case tipoFuncion.avg:
cmd.Append(" AVG(");
cmd.Append(columnaResumen);
cmd.Append(") ");
break;
case tipoFuncion.max:
cmd.Append(" MAX(");
cmd.Append(columnaResumen);
cmd.Append(") ");
break;
case tipoFuncion.min:
cmd.Append(" MIN(");
cmd.Append(columnaResumen);
cmd.Append(") ");
break;
case tipoFuncion.suma:
cmd.Append(" SUM(");
cmd.Append(columnaResumen);
cmd.Append(") ");
break;
}
cmd.Append(" from dbo.#temp ");
// lista de grupos
int agrupados = 0;
for (int i = 0; i < campos.Count; i++)
{
if (elegidos[i] == 0)
{
if (agrupados > 0)
{
cmd.Append(",");
}
else {
cmd.Append(" group by ");
}
cmd.Append(campos[i]);
agrupados++;
}
}
cmd.Append(" drop table dbo.#temp ");
String debug = cmd.ToString();
db.ejecutaComando(cmd.ToString());
}
